Re: query problem

On Wed, 2004-10-13 at 11:03 -0400, Tom Lane wrote:
> Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se> writes:
> > I sent this to general earlier but I was redirected to performance.
>
> Actually, I think I suggested that you consult the pgsql-performance
> archives, where this type of problem has been hashed out before.
> See for instance this thread:
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00169.php
> particularly
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00175.php
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00184.php
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00185.php
> which show three different ways of getting the planner to do something
> sane with an index range bound like "now() - interval".

Using exact timestamp makes the query go back as it should in speed (see
explain below). However I still have the problem using a stored
procedure or even using the "ago"-example from above.

regards,
Robin

status=# explain analyse
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 (SELECT machine_id FROM
machine_group_xref WHERE group_id = 1) AND
status-# '2004-10-13 17:47:36.902062' < data.entered
status-# ;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=3.09..481.28 rows=777 width=24) (actual
time=0.637..1.804 rows=57 loops=1)
Hash Cond: ("outer".template_id = "inner".id)
-> Nested Loop (cost=1.17..467.71 rows=776 width=24) (actual
time=0.212..1.012 rows=57 loops=1)
-> Hash IN Join (cost=1.17..9.56 rows=146 width=16) (actual
time=0.165..0.265 rows=9 loops=1)
Hash Cond: ("outer".machine_id = "inner".machine_id)
-> Index Scan using idx_d_entered on data
(cost=0.00..6.14 rows=159 width=16) (actual time=0.051..0.097 rows=10
loops=1)
Index Cond: ('2004-10-13
17:47:36.902062'::timestamp without time zone < entered)
-> Hash (cost=1.14..1.14 rows=11 width=4) (actual
time=0.076..0.076 rows=0 loops=1)
-> Seq Scan on machine_group_xref
(cost=0.00..1.14 rows=11 width=4) (actual time=0.017..0.054 rows=11
loops=1)
Filter: (group_id = 1)
-> Index Scan using idx_data_values_data_id on data_values
(cost=0.00..3.07 rows=5 width=16) (actual time=0.018..0.047 rows=6
loops=9)
Index Cond: (data_values.data_id = "outer".id)
-> 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.248 rows=74 loops=1)
Total runtime: 2.145 ms
(15 rows)