Hmmm [ studies query a bit more... ] I think the reason why that index
is so expensive to use is exposed here:
Index Cond: ((detect_time > (now() - '7
days'::interval)) AND (detect_time >= '2006-10-30
16:17:45.064793'::timestamp without time zone))
Evidently detect_time is timestamp without time zone, but you're
comparing it to an expression that is timestamp with time zone
(ie CURRENT_TIMESTAMP). That's an enormously expensive operator
compared to straight comparisons of two timestamps of the same ilk,
because it does some expensive stuff to convert across time zones.
And you're applying it to a whole lot of index rows.
If you change the query to use LOCALTIMESTAMP to avoid the type
conversion, how do the two plans compare?
regards, tom lane
OK, I recreated the index, and... you were right. Actually, it was my
query that sucked.
Index Cond: ((detect_time > (now() - '7
days'::interval)) AND (detect_time >= '2006-10-30
16:17:45.064793'::timestamp without time zone))
Is it greater() which returns the highest of two values ? (like max() but
not aggregate)
Anyway, I fixed this in the code that generates the query, it's cleaner.
So now, I just put a constant timestamp.
Then we have this interesting side effect. Simply changing the timestamp
value induces a different plan, and the one which returns more rows is
actually faster !
annonces=> EXPLAIN ANALYZE SELECT * FROM annonces AS a
WHERE
detect_time >= '2007-03-27 20:46:29.187131+02'
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=1422.91..6758.82 rows=1130
width=691) (actual time=27.007..27.542 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 >= '2007-03-27 20:46:29.187131'::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=1422.91..1422.91 rows=2367 width=0) (actual
time=26.960..26.960 rows=0 loops=1)
-> BitmapOr (cost=133.80..133.80 rows=4368 width=0) (actual
time=2.764..2.764 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.503..0.503 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.457..0.457 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.802..1.802 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
-> Bitmap Index Scan on annonces_timestamp (cost=0.00..1288.58
rows=69375 width=0) (actual time=23.906..23.906 rows=68022 loops=1)
Index Cond: (detect_time >= '2007-03-27
20:46:29.187131'::timestamp without time zone)
Total runtime: 27.669 ms
(14 lignes)
annonces=> EXPLAIN ANALYZE SELECT * FROM annonces AS a WHERE
detect_time >= '2006-03-27 20:46:29.187131+02'
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=134.51..7551.69 rows=2086
width=691) (actual time=3.372..6.517 rows=1063 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 >= '2006-03-27 20:46:29.187131'::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=134.51..134.51 rows=4368 width=0) (actual
time=2.643..2.643 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.537..0.537 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.385..0.385 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.719..1.719 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
Total runtime: 7.129 ms
