Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVq6K-0002GO-RY for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 20:50:25 +0000 Received: from pro-open.de ([87.106.136.39] helo=www.pro-open.de) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVq6I-0002TM-W5 for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 20:50:24 +0000 Received: from [192.168.1.9] (ip-176-198-223-42.unitymediagroup.de [176.198.223.42]) by www.pro-open.de (Postfix) with ESMTP id 026A6100A0A4; Tue, 6 Nov 2012 21:50:21 +0100 (CET) Message-ID: <5099780D.8060606@pro-open.de> Date: Tue, 06 Nov 2012 21:50:21 +0100 From: "Gunnar \"Nick\" Bluth" User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121028 Thunderbird/16.0.2 MIME-Version: 1.0 To: Petr Praus CC: Marcos Ortiz , pgsql-performance@postgresql.org, klaussfreire@gmail.com, jeff.janes@gmail.com Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries References: <5092DE42.5060206@uci.cu> <509304DC.1090809@pro-open.de> <5094F280.6010606@pro-open.de> <50954FAF.3060905@pro-open.de> <50962BCB.7020709@pro-open.de> <50996746.5010508@pro-open.de> <50997077.1020500@pro-open.de> In-Reply-To: Content-Type: multipart/alternative; boundary="------------020205030209010708030402" X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201211/73 X-Sequence-Number: 48439 This is a multi-part message in MIME format. --------------020205030209010708030402 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Am 06.11.2012 21:24, schrieb Petr Praus: > On 6 November 2012 14:17, Gunnar "Nick" Bluth > > 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? Well, you do have indices, as we can clearly see. > 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. I had been wondering why you didn't have any indices, tbth. However, the execution times still grow with work_mem, which is interesting independent of the actual plan change... > > Thanks for your help by the way! :-) Oh, no worries there... this is by far the most interesting challenge I've encountered in months ;-) But I do admit that I've reached the end of the ladder now. No idea how you can improve your runtime yet. Probably - using full text search on "personinfo" - try different join_collapse_limit / from_collapse_limit / enable_hashjoin values The most pragmatic approach is probably to just stick with work_mem = 1MB (or less) ;-), but that may potentially bite you later. -- 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 --------------020205030209010708030402 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 06.11.2012 21:24, schrieb Petr Praus:
On 6 November 2012 14:17, Gunnar "Nick" Bluth <gunn= ar.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?

Well, you do have indices, as we can clearly see.

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.

I had been wondering why you didn't have any indices, tbth. However, the execution times still grow with work_mem, which is interesting independent of the actual plan change...


Thanks for your help by the way! :-)
=C2=A0

Oh, no worries there... this is by far the most interesting challenge I've encountered in months ;-)

But I do admit that I've reached the end of the ladder now. No idea how you can improve your runtime yet. Probably
- using full text search on "personinfo"
- try different join_collapse_limit / from_collapse_limit / enable_hashjoin values

The most pragmatic approach is probably to just stick with work_mem =3D 1MB (or less) ;-), but that may potentially bite you later.

--=20
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 yo=
u
getting? - Tom Payne
--------------020205030209010708030402--