On Fri, 2004-10-08 at 10:11 -0400, Tom Lane wrote: > Robin Ericsson <[EMAIL PROTECTED]> writes: > > -> Index Scan using idx_d_entered on data (cost=0.00..18024.04 > > rows=50360 width=16) (actual time=0.210..0.247 rows=1 loops=1) > > Index Cond: (((('now'::text)::timestamp(6) with time > > zone)::timestamp without time zone - '00:01:00'::interval) < entered) > > You're running into the well-known problem that the planner can't make > good estimates for index conditions that involve non-constant terms > (such as CURRENT_TIMESTAMP). Lacking a decent estimate, it guesses that > this scan will produce many more rows than it really will, and so it > tends to favor plans that would be good in that scenario, but are not > optimal for retrieving just a couple of rows. > > One workaround is to do the date arithmetic on the client side; another > is to cheat by hiding the arithmetic in a function like "ago(interval)" > that you lyingly claim is IMMUTABLE. See the pgsql-performance > archives.
I did run a new explain analyze on the query and found the attached result. status=# EXPLAIN ANALYZE status-# SELECT status-# data.entered, status-# data.machine_id, status-# datatemplate_intervals.template_id, status-# data_values.value status-# FROM status-# data, data_values, datatemplate_intervals status-# WHERE status-# datatemplate_intervals.id = data_values.template_id AND status-# data_values.data_id = data.id AND status-# data.machine_id IN (2,3) AND status-# current_timestamp::timestamp - interval '60 seconds' < data.entered; It seems very strange that it does a full index scan on idx_dv_data_id. Regards, Robin
Hash Join (cost=28646.01..274260.15 rows=555706 width=24) (actual time=102323.087..102323.196 rows=5 loops=1) Hash Cond: ("outer".template_id = "inner".id) -> Merge Join (cost=28644.09..265922.62 rows=555706 width=24) (actual time=102322.632..102322.709 rows=5 loops=1) Merge Cond: ("outer".data_id = "inner".id) -> Index Scan using idx_dv_data_id on data_values (cost=0.00..205034.19 rows=9580032 width=16) (actual time=17.503..86263.130 rows=9596747 loops=1) -> Sort (cost=28644.09..28870.83 rows=90697 width=16) (actual time=0.829..0.835 rows=1 loops=1) Sort Key: data.id -> Index Scan using idx_d_entered on data (cost=0.00..20202.81 rows=90697 width=16) (actual time=0.146..0.185 rows=1 loops=1) Index Cond: (((('now'::text)::timestamp(6) with time zone)::timestamp without time zone - '00:01:00'::interval) < entered) Filter: ((machine_id = 2) OR (machine_id = 3)) -> Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382 rows=0 loops=1) -> Seq Scan on datatemplate_intervals (cost=0.00..1.74 rows=74 width=8) (actual time=0.024..0.250 rows=74 loops=1) Total runtime: 102323.491 ms (13 rows)
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]