Re: query problem - Mailing list pgsql-performance

On Wed, 2004-10-13 at 11:03 -0400, Tom Lane wrote:
> Robin Ericsson <robin.ericsson@profecta.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)