Hello everyone !
I have this query :
annonces=> EXPLAIN ANALYZE SELECT * FROM annonces AS a WHERE
detect_time > CURRENT_TIMESTAMP - '7 DAY'::INTERVAL
AND detect_time >= '2006-10-30 16:17:45.064793'
AND vente
AND surface IS NOT NULL AND price IS NOT NULL
AND type_id IN
(1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6)
AND (a.zipcode IN (69001,69002,69003,69004,69005,69006,69007,69008,69009)
OR a.city_id IN (27595)
OR a.coords &&
'(45.74101689082,4.8371263505564),(45.75898310918,4.8628736494436)'::BOX) ;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on annonces a (cost=1657.06..7145.98 rows=1177
width=691) (actual time=118.342..118.854 rows=194 loops=1)
Recheck Cond: (((vente AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
OR (vente AND (city_id = 27595)) OR (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box))
AND (detect_time > (now() - '7 days'::interval)) AND (detect_time >=
'2006-10-30 16:17:45.064793'::timestamp without time zone))
Filter: (vente AND (surface IS NOT NULL) AND (price IS NOT NULL) AND
(type_id = ANY
('{1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6}'::integer[])))
-> BitmapAnd (cost=1657.06..1657.06 rows=2465 width=0) (actual
time=118.294..118.294 rows=0 loops=1)
-> BitmapOr (cost=133.83..133.83 rows=4368 width=0) (actual
time=2.903..2.903 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.599..0.599 rows=1580 loops=1)
Index Cond: ((vente = true) AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
-> Bitmap Index Scan on annonces_city (cost=0.00..43.30
rows=1904 width=0) (actual time=0.464..0.464 rows=1575 loops=1)
Index Cond: ((vente = true) AND (city_id = 27595))
-> Bitmap Index Scan on annonces_coords (cost=0.00..33.10
rows=640 width=0) (actual time=1.837..1.837 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
-> Bitmap Index Scan on annonces_date (cost=0.00..1522.68
rows=72241 width=0) (actual time=114.930..114.930 rows=68022 loops=1)
Index Cond: ((detect_time > (now() - '7 days'::interval))
AND (detect_time >= '2006-10-30 16:17:45.064793'::timestamp without time
zone))
Total runtime: 119.000 ms
(14 lignes)
The interesting part is :
Bitmap Index Scan on annonces_date (cost=0.00..1522.68 rows=72241
width=0) (actual time=114.930..114.930 rows=68022 loops=1)
It bitmapscans about half the table...
I realized this index was actually useless for all my queries, so I
dropped it, and behold :
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on annonces a (cost=133.83..7583.77 rows=1176
width=691) (actual time=5.483..18.731 rows=194 loops=1)
Recheck Cond: ((vente AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
OR (vente AND (city_id = 27595)) OR (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box))
Filter: ((detect_time > (now() - '7 days'::interval)) AND (detect_time
= '2006-10-30 16:17:45.064793'::timestamp without time zone) AND vente
AND (surface IS NOT NULL) AND (price IS NOT NULL) AND (type_id = ANY
('{1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6}'::integer[])))
-> BitmapOr (cost=133.83..133.83 rows=4368 width=0) (actual
time=2.648..2.648 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.505..0.505 rows=1580 loops=1)
Index Cond: ((vente = true) AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
-> Bitmap Index Scan on annonces_city (cost=0.00..43.30
rows=1904 width=0) (actual time=0.415..0.415 rows=1575 loops=1)
Index Cond: ((vente = true) AND (city_id = 27595))
-> Bitmap Index Scan on annonces_coords (cost=0.00..33.10
rows=640 width=0) (actual time=1.725..1.725 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
Total runtime: 18.859 ms
I guess saving 68022 rows of index scan is worth it !
Now 18 ms to extract the rows I want from that table (not huge, but still
about 70 megabytes) is what I call : nice.
Just thought it might be useful to some of you. I should have remembered
KISS !
BTW, shouldn't the planner think about this also ? Bitmap-scanning half a
table is likely to be slower than seq scan anyway...
