Re: slow query performance

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu <fotographs(at)gmail(dot)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