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]
