Re: slow query performance

The plan is unaltered . There is a separate index on theDate as well
as one on node_id

I have not specifically disabled sequential scans.

This query performs much better on 8.1.9 on a similar sized
table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )

On Wed, Jun 9, 2010 at 7:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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
>