X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AFA8432A684 for ; Wed, 13 Oct 2004 16:54:29 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37055-06 for ; Wed, 13 Oct 2004 15:54:24 +0000 (GMT) Received: from zaphod.profecta.se (profecta1.cust.morotsmedia.se [193.235.206.49]) by svr1.postgresql.org (Postfix) with ESMTP id A62A332A465 for ; Wed, 13 Oct 2004 16:54:22 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by zaphod.profecta.se (Postfix) with ESMTP id 60E7A10F4; Wed, 13 Oct 2004 17:54:08 +0200 (CEST) Received: from [192.168.128.80] (ip050.noname4us.com [212.247.87.50]) by zaphod.profecta.se (Postfix) with ESMTP id 06659A57; Wed, 13 Oct 2004 17:54:08 +0200 (CEST) Subject: Re: query problem From: Robin Ericsson To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <14684.1097679793@sss.pgh.pa.us> References: <1097659271.24018.68.camel@pylver.localhost.nu.> <14684.1097679793@sss.pgh.pa.us> Content-Type: text/plain Organization: Profecta HB Date: Wed, 13 Oct 2004 18:01:10 +0200 Message-Id: <1097683270.24018.124.camel@pylver.localhost.nu.> Mime-Version: 1.0 X-Mailer: Evolution 2.0.0 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/175 X-Sequence-Number: 8651 On Wed, 2004-10-13 at 11:03 -0400, Tom Lane wrote: > Robin Ericsson 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)