Hi all,
I need your help on a small performance problem.
I have a table of which I have to do a bunch of counts of various
conditions. The worst case scenario where I have to iterate over every
record in the table performs just a little bit too slow (800ms). That
particular query will be hit a lot (it will be on the index of our web app).
PostgreSQL uses a sequential scan (it should IMO) - I think my
bottleneck is in the operators on the various columns.
My queries look like this:
SELECT COUNT(NULLIF(max_persons BETWEEN 5 AND 8, false)) AS "persons 5-8",
-- And other variations
COUNT(NULLIF(country_id = 74, false)) AS "LOCATION_NETHERLANDS",
-- Basically for every country in Europe
COUNT(NULLIF(specifications & '00000000000000000000000001000000',
0::bit(32))) AS "washing machine",
-- And a bunch more of these; the bit mask is almost fully covered
COUNT(*) AS all
FROM table;
The plan is:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7371.23..7371.55 rows=1 width=18) (actual
time=803.374..803.376 rows=1 loops=1)
-> Seq Scan on fewo_property_location (cost=0.00..828.84 rows=41538
width=18) (actual time=0.036..147.211 rows=41492 loops=1)
Filter: ((location_id IS NOT NULL) AND (property_state_id = 3))
Total runtime: 804.398 ms
(4 rows)
The table definition is like:
Column | Type | Modifiers
-------------------+----------+----------------------
property_id | integer | not null
property_state_id | integer | not null
location_id | integer |
min_persons | smallint | not null
max_persons | smallint | not null
specifications | bit(32) | default (0)::bit(32)
country_id | integer |
Indexes:
"fewo_property_location_pkey" PRIMARY KEY, btree (property_id)
"fewo_property_location_country_idx" btree (country_id) WHERE
location_id IS NOT NULL
"fewo_property_location_country_location_idx" btree (country_id,
location_id) CLUSTER
"fewo_property_location_location_online_idx" btree (location_id)
WHERE location_id IS NOT NULL AND property_state_id = 3
"fewo_property_location_property_location_idx" btree (property_id,
location_id) WHERE location_id IS NOT NULL AND property_state_id = 3
"fewo_property_location_specifications_idx" btree (specifications)
Foreign-key constraints:
"fewo_property_location_location_id_fkey" FOREIGN KEY (location_id)
REFERENCES fewo_location(location_id) MATCH FULL
"fewo_property_location_property_state_id_fkey" FOREIGN KEY
(property_state_id) REFERENCES fewo_property_state(property_state_id)
MATCH FULL
My conclusion is that this query time is mostly limited to the somewhat
complex COUNT expressions. Is there any way to do this more efficiently?
For the record, if I constrain this query to specific countries it
performs in about 80ms (10x as fast).
The hardware is a dual Opteron64x2, 4G RAM and some kind of RAID setup
(software, don't know what type) running in a Xen host - it's our
development DB-server.
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
