Postgres Pro
Downloads
Home   >   mailing lists

Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries - Mailing list pgsql-performance

From Petr Praus
Subject Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Date
Msg-id CACezXZ9OL7BmngxwcYzGouw-Jz-0AkBQCQLGwOUvvPiHbebiyA@mail.gmail.com
Whole thread Raw
In response to Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries  ("Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>)
List pgsql-performance
Tree view
On 6 November 2012 14:17, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:
Am 06.11.2012 21:08, schrieb Petr Praus:


2MB: http://explain.depesz.com/s/ul1
4MB: http://explain.depesz.com/s/IlVu
10MB: http://explain.depesz.com/s/afx3
12MB: http://explain.depesz.com/s/i0vQ

See the change in the plan between 10MB and 12MB, directly at top level? That narrows the thing down quite a bit.

Though I wonder why this didn't show in the original plans...

Yes, the 2,4 and 10 are the same, the only difference is number of buckets. But with 12, it makes completely different choices, it decides to make sequential scans and hash right joins instead of merge joins. And those sequential scans take a loong time. Could this be caused by some missing indices perhaps?

The original plans I posted at the start are the same as the 12MB plan, I'm not sure why is that, I really hope I didn't make some sort of mistake there.

Thanks for your help by the way! :-)
 


--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne


pgsql-performance by date:

Previous
From: Petr Praus
Date:
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Next
From: aasat
Date:
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2