pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed8.1 -> 8.4 regression
7+ messages / 4 participants
[nested] [flat]
* 8.1 -> 8.4 regression
@ 2010-02-14 18:38 Ben Chobot <bench@silentmedia.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ben Chobot @ 2010-02-14 18:38 UTC (permalink / raw)
To: pgsql-performance
(Apologies if this ends up coming through multiple times - my first attempts seem to have gotten stuck.)
We recently took the much needed step in moving from 8.1.19 to 8.4.2. We took the downtime opportunity to also massively upgrade our hardware. Overall, this has been the major improvement you would expect, but there is at least one query which has degraded in performance quite a bit. Here is the plan on 8.4.2:
http://wood.silentmedia.com/bench/842
Here is the very much less compact plan for the same query on 8.1.19:
http://wood.silentmedia.com/bench/8119
I think the problem might be that 8.1.19 likes to use a few indexes which 8.4.2 doesn't seem to think would be worthwhile. Perhaps that's because on the new hardware almost everything fits into ram, but even so, it would be better if those indexes were used. The other differences I can think of are random_page_cost (2 on the new hardware vs. 2.5 on the old), a ten-fold increase in effective_cache_size, doubling work_mem from 8MB to 16MB, and that we analyze up to 100 samples per attribute on 8.4.2, while our 8.1.19 install does 10 at most. Still, the estimates for both plans seem fairly accurate, at least where there are differences in which indexes are getting used.
Everything has been analyzed recently, and given that 8.4.2 already has 10x more analysis samples than 8.1.19, I'm not sure what to do to coax it towards using those indexes.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.1 -> 8.4 regression
@ 2010-02-15 09:40 Dennis Björklund <db@zigo.dhs.org>
parent: Ben Chobot <bench@silentmedia.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dennis Björklund @ 2010-02-15 09:40 UTC (permalink / raw)
To: Ben Chobot <bench@silentmedia.com>; +Cc: pgsql-performance
Can you force 8.4 to generate the same plan as 8.1? For example by running
SET enable_hashjoin = off;
before you run EXPLAIN on the query? If so, then we can compare the
numbers from the forced plan with the old plan and maybe figure out why it
didn't use the same old plan in 8.4 as it did in 8.1.
Note that the solution is not to force the plan, but it can give us more
information.
/Dennis
> is at least one query which has degraded in performance quite a bit. Here
> is the plan on 8.4.2:
> http://wood.silentmedia.com/bench/842
>
> Here is the very much less compact plan for the same query on 8.1.19:
> http://wood.silentmedia.com/bench/8119
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.1 -> 8.4 regression
@ 2010-02-15 15:59 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2010-02-15 15:59 UTC (permalink / raw)
To: pgsql-performance; bench@silentmedia.com
Ben Chobot wrote:
> Here is the plan on 8.4.2:
> Here is the very much less compact plan for the same query on
> 8.1.19:
Could you show the query, along with table definitions (including
indexes)?
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.1 -> 8.4 regression
@ 2010-02-15 16:16 Ben Chobot <bench@silentmedia.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Ben Chobot @ 2010-02-15 16:16 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
> Could you show the query, along with table definitions (including
> indexes)?
Oh, yeah, I suppose that would help. :)
http://wood.silentmedia.com/bench/query_and_definitions
(I'd paste them here for posterity but I speculate the reason my first few attempts to ask this question never went through were because of the size of the email.)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.1 -> 8.4 regression
@ 2010-02-15 17:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ben Chobot <bench@silentmedia.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2010-02-15 17:26 UTC (permalink / raw)
To: Ben Chobot <bench@silentmedia.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
Ben Chobot <bench@silentmedia.com> writes:
> On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
>> Could you show the query, along with table definitions (including
>> indexes)?
> Oh, yeah, I suppose that would help. :)
> http://wood.silentmedia.com/bench/query_and_definitions
It looks like the problem is that the EXISTS sub-query is getting
converted into a join; which is usually a good thing but in this case it
interferes with letting the users table not be scanned completely.
The long-term fix for that is to support nestloop inner indexscans where
the index key comes from more than one join level up, but making that
happen isn't too easy.
In the meantime, I think you could defeat the "optimization" by
inserting LIMIT 1 in the EXISTS sub-query.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.1 -> 8.4 regression
@ 2010-02-15 17:35 Ben Chobot <bench@silentmedia.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Ben Chobot @ 2010-02-15 17:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
Awesome, that did the trick. Thanks Tom! So I understand better, why is my case not the normal, better case?
(I assume the long-term fix is post-9.0, right?)
On Feb 15, 2010, at 9:26 AM, Tom Lane wrote:
> Ben Chobot <bench@silentmedia.com> writes:
>> On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
>>> Could you show the query, along with table definitions (including
>>> indexes)?
>
>> Oh, yeah, I suppose that would help. :)
>
>> http://wood.silentmedia.com/bench/query_and_definitions
>
> It looks like the problem is that the EXISTS sub-query is getting
> converted into a join; which is usually a good thing but in this case it
> interferes with letting the users table not be scanned completely.
> The long-term fix for that is to support nestloop inner indexscans where
> the index key comes from more than one join level up, but making that
> happen isn't too easy.
>
> In the meantime, I think you could defeat the "optimization" by
> inserting LIMIT 1 in the EXISTS sub-query.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.1 -> 8.4 regression
@ 2010-02-17 05:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ben Chobot <bench@silentmedia.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2010-02-17 05:34 UTC (permalink / raw)
To: Ben Chobot <bench@silentmedia.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
Ben Chobot <bench@silentmedia.com> writes:
> Awesome, that did the trick. Thanks Tom! So I understand better, why is my case not the normal, better case?
Well, the short answer is that the 8.4 changes here are in the nature of
two steps forward and one step back. The long-term goal is to increase
the planner's ability to choose among different join orders; but we're
getting rid of one restriction at a time, and sometimes the interactions
of those restrictions produce unwanted results like the older code being
able to find a better plan than the new code can.
> (I assume the long-term fix is post-9.0, right?)
Yeah, fraid so. I've been mostly buried in non-planner work in the 9.0
cycle, but hope to get back to this and other problems in the next
cycle.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-02-17 05:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-14 18:38 8.1 -> 8.4 regression Ben Chobot <bench@silentmedia.com>
2010-02-15 09:40 ` Dennis Björklund <db@zigo.dhs.org>
2010-02-15 15:59 Re: 8.1 -> 8.4 regression Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-15 16:16 ` Ben Chobot <bench@silentmedia.com>
2010-02-15 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-15 17:35 ` Ben Chobot <bench@silentmedia.com>
2010-02-17 05:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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