pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Gunnar \"Nick\" Bluth <gunnar.bluth@pro-open.de>
To: Petr Praus <petr@praus.net>
Cc: Marcos Ortiz <mlortiz@uci.cu>
Cc: pgsql-performance@postgresql.org
Cc: klaussfreire@gmail.com
Cc: jeff.janes@gmail.com
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Date: Tue, 06 Nov 2012 21:50:21 +0100
Message-ID: <5099780D.8060606@pro-open.de> (raw)
In-Reply-To: <CACezXZ9OL7BmngxwcYzGouw-Jz-0AkBQCQLGwOUvvPiHbebiyA@mail.gmail.com>
References: <CACezXZ_w7HbqSxZ=5SJH=kxb4nBDNbpDejttSaU6EC1AeO4PYg@mail.gmail.com>
	<CACezXZ_UYbzvNEP_V6-bsyzGij-kJdeNz04-o=kbMXmcLMHnqw@mail.gmail.com>
	<5092DE42.5060206@uci.cu>
	<509304DC.1090809@pro-open.de>
	<CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmail.com>
	<5094F280.6010606@pro-open.de>
	<CACezXZ9SV58CbFM3-MpKGm1z-J0Ho1zERMUBuLH6QhC7RJ5T8Q@mail.gmail.com>
	<50954FAF.3060905@pro-open.de>
	<CACezXZ-NBmEd-Ur8TKvi2fhJkLAC=7w4y93ZPPzoFa1rZH+nmg@mail.gmail.com>
	<50962BCB.7020709@pro-open.de>
	<CACezXZ96RGOOR=vzCOA+Zv-froXSMr9QmJQCJRp_9ndtkszbzQ@mail.gmail.com>
	<50996746.5010508@pro-open.de>
	<CACezXZ8Tp90V+ahNbkZPZtiSFDZpWnQ+6WoBxoPMXAKbMn+GEQ@mail.gmail.com>
	<50997077.1020500@pro-open.de>
	<CACezXZ9OL7BmngxwcYzGouw-Jz-0AkBQCQLGwOUvvPiHbebiyA@mail.gmail.com>

Am 06.11.2012 21:24, schrieb Petr Praus:
> On 6 November 2012 14:17, Gunnar "Nick" Bluth 
> <gunnar.bluth@pro-open.de <mailto: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?

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

view thread (27+ messages)  latest in thread

Message-ID: <5099780D.8060606@pro-open.de>
Permalink:  ../5099780D.8060606@pro-open.de/
Also on:    postgresql.org/message-id/5099780D.8060606@pro-open.de

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gunnar.bluth@pro-open.de, petr@praus.net, mlortiz@uci.cu, klaussfreire@gmail.com, jeff.janes@gmail.com
  Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
  In-Reply-To: <5099780D.8060606@pro-open.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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