From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Anj Adu <fotographs@gmail.com>
Cc: Andy Colson <andy@squeakycode.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow query performance
Date: Wed, 09 Jun 2010 22:55:20 -0400
Message-ID: <5860.1276138520@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTilD4kN8bc7I3XauXEaryvsEZ-mXebx5yk71Vv6r@mail.gmail.com>
References: <AANLkTilbM1iWSrrchOB9x1_W2CKQSTzCow6BznV1DxNk@mail.gmail.com>
<4C07F7C9.3060401@squeakycode.net>
<AANLkTin5Ukiz3JfELSuUEDDk1VvRfmmx913-bXer06NA@mail.gmail.com>
<AANLkTilD4kN8bc7I3XauXEaryvsEZ-mXebx5yk71Vv6r@mail.gmail.com>
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu <fotographs@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, fotographs@gmail.com, andy@squeakycode.net
Subject: Re: slow query performance
In-Reply-To: <5860.1276138520@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox