Re: query gone haywire :)

On Fri, 2004-10-08 at 10:11 -0400, Tom Lane wrote:
> Robin Ericsson <robin.ericsson@profecta.se> writes:
> >          ->  Index Scan using idx_d_entered on data  (cost=0.00..18024.04 rows=50360 width=16) (actual
time=0.210..0.247rows=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