From: | Anj Adu <fotographs(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Andy Colson <andy(at)squeakycode(dot)net>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow query performance |
Date: | 2010-06-10 03:17:03 |
Message-ID: | AANLkTilVl4gsq7dHFjy6BZ9clP6V9Rqgb6u-5rzDcEBc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Max Williams | 2010-06-10 08:18:12 | Re: Large (almost 50%!) performance drop after upgrading to 8.4.4? |
Previous Message | Tom Lane | 2010-06-10 02:55:20 | Re: slow query performance |