From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Pallav Kalva <pkalva(at)livedatagroup(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: 8.2.4 Chooses Bad Query Plan |
Date: | 2007-08-28 18:36:13 |
Message-ID: | 1206.1188326173@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Pallav Kalva <pkalva(at)livedatagroup(dot)com> writes:
> We have recently upgraded our production database from 8.0.12 to
> 8.2.4, We have seen lot of improvements on 8.2.4 side but we are also
> seeing some queries which are slow.
> Particularly this below query is really bad in 8.2.4 , I can get
> only the explain on this as explain analyze never finishes even after 20
> min.
What it's doing is scanning backward on activity1_.activityid and hoping
to find a row that matches all the other constraints soon enough to make
that faster than any other way of doing the query. 8.0 would have done
the same thing, I believe, if the statistics looked favorable for it.
So I wonder if you've forgotten to re-ANALYZE your data since migrating
(a pg_dump script won't do this for you).
> -> Index Scan using idx_accountactivity_fkactivityid on
> accountactivity accountact0_ (cost=0.00..3.94 rows=1 width=16)
> Index Cond: (accountact0_.fkactivityid =
> activity1_.activityid)
> Filter: (fkaccountid = 1455437)
> -> Index Scan using
> idx_accountactivity_fkaccountid on accountactivity accountact0_
> (cost=0.00..1641.42 rows=1343 width=16) (actual time=115.348..864.416
> rows=10302 loops=1)
> Index Cond: (fkaccountid = 1455437)
The discrepancy in rowcount estimates here is pretty damning.
Even the 8.0 estimate wasn't really very good --- you might want to
consider increasing default_statistics_target.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-08-28 18:48:00 | Re: Fwd: 8.2 Query 10 times slower than 8.1 (view-heavy) |
Previous Message | Anton Melser | 2007-08-28 18:36:11 | Re: Postgres performance problem |