Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id D7123632C49 for ; Wed, 9 Jun 2010 23:55:30 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 58061-03 for ; Thu, 10 Jun 2010 02:55:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 430C963275D for ; Wed, 9 Jun 2010 23:55:23 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o5A2tKH6005861; Wed, 9 Jun 2010 22:55:20 -0400 (EDT) To: Robert Haas cc: Anj Adu , Andy Colson , pgsql-performance@postgresql.org Subject: Re: slow query performance In-reply-to: References: <4C07F7C9.3060401@squeakycode.net> Comments: In-reply-to Robert Haas message dated "Wed, 09 Jun 2010 22:12:41 -0400" Date: Wed, 09 Jun 2010 22:55:20 -0400 Message-ID: <5860.1276138520@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201006/143 X-Sequence-Number: 38999 Robert Haas writes: > On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu wrote: >> Link to plan >> >> http://explain.depesz.com/s/kHa > Your problem is likely related to the line that's showing up in red: > Index Scan using dev4_act_dy_fact_2010_05_t3_thedate on > dev4_act_dy_fact_2010_05_t3 a (cost=0.00..94041.89 rows=204276 > width=60) (actual time=164533.725..164533.725 rows=0 loops=1) > * Index Cond: ((thedate >= '2010-05-22 00:00:00'::timestamp > without time area) AND (thedate <= '2010-05-22 00:00:00'::timestamp > without time area)) > * Filter: (node_id = $0) "timestamp without time area"? Somehow I think this isn't the true unaltered output of EXPLAIN. I'm just guessing, since we haven't been shown any table schemas, but what it looks like to me is that the planner is using an entirely inappropriate index in which the "thedate" column is a low-order column. So what looks like a nice tight indexscan range is actually a full-table indexscan. The planner knows that this is ridiculously expensive, as indicated by the high cost estimate. It would be cheaper to do a seqscan, which leads me to think the real problem here is the OP has disabled seqscans. It might be worth providing an index in which "thedate" is the only, or at least the first, column. For this particular query, an index on node_id and thedate would actually be ideal, but that might be too specialized. regards, tom lane