Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

Hello,

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with
16GB of RAM. The server is dedicated to this database, the disks are local
RAID10. Given that the default postgresql.conf is quite conservative
regarding memory settings, I thought it might be a good idea to allow
Postgres to use more memory. To my surprise, following advice in the
performance tuning guide on Postgres wiki[2] significantly slowed down
practically every query I run but it's more noticeable on the more complex
queries.

I also tried running pgtune[1] which gave the following recommendation with
more parameters tuned, but that didn't change anything. It suggests
shared_buffers of 1/4 of RAM size which seems to in line with advice
elsewhere (and on PG wiki in particular).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

I tried reindexing the whole database after changing the settings (using
REINDEX DATABASE), but that didn't help either. I played around with
shared_buffers and work_mem. Gradually changing them from the very
conservative default values (128k / 1MB) also gradually decreased
performance.

I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to
be that Hash Join is significantly slower. It's not clear to me why.

To give some specific example, I have the following query. It runs in
~2100ms on the default configuration and ~3300ms on the configuration with
increased buffer sizes:

select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) for the query above:

- Default buffers: http://explain.depesz.com/s/xaHJ
- Bigger buffers: http://explain.depesz.com/s/Plk

The tables don't have anything special in them

The question is why am I observing decreased performance when I increase
buffer sizes? The machine is definitely not running out of memory.
Allocation if shared memory in OS is (`shmmax` and `shmall`) is set to very
large values, that should not be a problem. I'm not getting any errors in
the Postgres log either. I'm running autovacuum in the default
configuration but I don't expect that has anything to do with it. All
queries were run on the same machine few seconds apart, just with changed
configuration (and restarted PG).

I also found a blog post [3] which experiments with various work_mem values
that run into similar behavior I'm experiencing but it doesn't really
explain it.

[1]: http://pgfoundry.org/projects/pgtune/
[2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
[3]:
http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/

Thanks,
Petr Praus

PS:
I also posted the question here:
http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-downbut
a few people suggested

I just found one particularly interesting fact: when I perform the same
test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB
RAM, I don't experience the slow down.
Specifically:
set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

When I do exactly the same query (the one from my previous post) with
exactly the same data on the server:
I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

The Mac has SSD so it's understandably faster, but it exhibits a behavior I
would expect. What am I doing wrong here?

Thanks.

On 30 October 2012 14:08, Petr Praus <petr(at)praus(dot)net> wrote:

> Hello,
>
> I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with
> 16GB of RAM. The server is dedicated to this database, the disks are local
> RAID10. Given that the default postgresql.conf is quite conservative
> regarding memory settings, I thought it might be a good idea to allow
> Postgres to use more memory. To my surprise, following advice in the
> performance tuning guide on Postgres wiki[2] significantly slowed down
> practically every query I run but it's more noticeable on the more complex
> queries.
>
> I also tried running pgtune[1] which gave the following recommendation
> with more parameters tuned, but that didn't change anything. It suggests
> shared_buffers of 1/4 of RAM size which seems to in line with advice
> elsewhere (and on PG wiki in particular).
>
> default_statistics_target = 50
> maintenance_work_mem = 960MB
> constraint_exclusion = on
> checkpoint_completion_target = 0.9
> effective_cache_size = 11GB
> work_mem = 96MB
> wal_buffers = 8MB
> checkpoint_segments = 16
> shared_buffers = 3840MB
> max_connections = 80
>
> I tried reindexing the whole database after changing the settings (using
> REINDEX DATABASE), but that didn't help either. I played around with
> shared_buffers and work_mem. Gradually changing them from the very
> conservative default values (128k / 1MB) also gradually decreased
> performance.
>
> I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to
> be that Hash Join is significantly slower. It's not clear to me why.
>
> To give some specific example, I have the following query. It runs in
> ~2100ms on the default configuration and ~3300ms on the configuration with
> increased buffer sizes:
>
> select count(*) from contest c
> left outer join contestparticipant cp on c.id=cp.contestId
> left outer join teammember tm on tm.contestparticipantid=cp.id
> left outer join staffmember sm on cp.id=sm.contestparticipantid
> left outer join person p on p.id=cp.personid
> left outer join personinfo pi on pi.id=cp.personinfoid
> where pi.lastname like '%b%' or pi.firstname like '%a%';
>
> EXPLAIN (ANALYZE,BUFFERS) for the query above:
>
> - Default buffers: http://explain.depesz.com/s/xaHJ
> - Bigger buffers: http://explain.depesz.com/s/Plk
>
> The tables don't have anything special in them
>
> The question is why am I observing decreased performance when I increase
> buffer sizes? The machine is definitely not running out of memory.
> Allocation if shared memory in OS is (`shmmax` and `shmall`) is set to very
> large values, that should not be a problem. I'm not getting any errors in
> the Postgres log either. I'm running autovacuum in the default
> configuration but I don't expect that has anything to do with it. All
> queries were run on the same machine few seconds apart, just with changed
> configuration (and restarted PG).
>
> I also found a blog post [3] which experiments with various work_mem
> values that run into similar behavior I'm experiencing but it doesn't
> really explain it.
>
> [1]: http://pgfoundry.org/projects/pgtune/
> [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> [3]:
> http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/
>
> Thanks,
> Petr Praus
>
> PS:
> I also posted the question here:
> http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-downbut a few people suggested
>

- I'm using ext4
- Kernel: Linux 2.6.32-279.9.1.el6.x86_64 #1 SMP Fri Aug 31 09:04:24 EDT
2012 x86_64 x86_64 x86_64 GNU/Linux
- I haven't tuned kernel in any way except setting kernel.shmmax and
kernel.shmall to:
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
- We are using 15k drives (magnetic) connected through SAS in RAID10 setup,
I don't know precise model numbers (I can find out),

On 1 November 2012 15:40, Marcos Ortiz <mlortiz(at)uci(dot)cu> wrote:

> Regards, Petr.
> Tuning PostgreSQL is not just change the postgresql.conf, it includes more
> things like:
> - the filesystem that you are using
> - the kernel version that you using (particularly in Linux systems)
> - the tuning to kernel variables
> - the type of discs that you are using (SSDs are very fast, like you saw
> in your iMac system)
>
>
> On 10/30/2012 02:44 PM, Petr Praus wrote:
>
> I just found one particularly interesting fact: when I perform the same
> test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB
> RAM, I don't experience the slow down.
> Specifically:
> set work_mem='1MB';
> select ...; // running time is ~1800 ms
> set work_mem='96MB';
> select ...' // running time is ~1500 ms
>
> When I do exactly the same query (the one from my previous post) with
> exactly the same data on the server:
> I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.
>
> The Mac has SSD so it's understandably faster, but it exhibits a
> behavior I would expect. What am I doing wrong here?
>
> Thanks.
>
> On 30 October 2012 14:08, Petr Praus <petr(at)praus(dot)net> wrote:
>
>> Hello,
>>
>> I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine
>> with 16GB of RAM. The server is dedicated to this database, the disks are
>> local RAID10. Given that the default postgresql.conf is quite conservative
>> regarding memory settings, I thought it might be a good idea to allow
>> Postgres to use more memory. To my surprise, following advice in the
>> performance tuning guide on Postgres wiki[2] significantly slowed down
>> practically every query I run but it's more noticeable on the more complex
>> queries.
>>
>> I also tried running pgtune[1] which gave the following recommendation
>> with more parameters tuned, but that didn't change anything. It suggests
>> shared_buffers of 1/4 of RAM size which seems to in line with advice
>> elsewhere (and on PG wiki in particular).
>>
>> default_statistics_target = 50
>> maintenance_work_mem = 960MB
>> constraint_exclusion = on
>> checkpoint_completion_target = 0.9
>> effective_cache_size = 11GB
>> work_mem = 96MB
>> wal_buffers = 8MB
>> checkpoint_segments = 16
>> shared_buffers = 3840MB
>> max_connections = 80
>>
>> I tried reindexing the whole database after changing the settings
>> (using REINDEX DATABASE), but that didn't help either. I played around with
>> shared_buffers and work_mem. Gradually changing them from the very
>> conservative default values (128k / 1MB) also gradually decreased
>> performance.
>>
>> I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems
>> to be that Hash Join is significantly slower. It's not clear to me why.
>>
>> To give some specific example, I have the following query. It runs in
>> ~2100ms on the default configuration and ~3300ms on the configuration with
>> increased buffer sizes:
>>
>> select count(*) from contest c
>> left outer join contestparticipant cp on c.id=cp.contestId
>> left outer join teammember tm on tm.contestparticipantid=cp.id
>> left outer join staffmember sm on cp.id=sm.contestparticipantid
>> left outer join person p on p.id=cp.personid
>> left outer join personinfo pi on pi.id=cp.personinfoid
>> where pi.lastname like '%b%' or pi.firstname like '%a%';
>>
>> EXPLAIN (ANALYZE,BUFFERS) for the query above:
>>
>> - Default buffers: http://explain.depesz.com/s/xaHJ
>> - Bigger buffers: http://explain.depesz.com/s/Plk
>>
>> The tables don't have anything special in them
>>
>> The question is why am I observing decreased performance when I
>> increase buffer sizes? The machine is definitely not running out of memory.
>> Allocation if shared memory in OS is (`shmmax` and `shmall`) is set to very
>> large values, that should not be a problem. I'm not getting any errors in
>> the Postgres log either. I'm running autovacuum in the default
>> configuration but I don't expect that has anything to do with it. All
>> queries were run on the same machine few seconds apart, just with changed
>> configuration (and restarted PG).
>>
>> I also found a blog post [3] which experiments with various work_mem
>> values that run into similar behavior I'm experiencing but it doesn't
>> really explain it.
>>
>> [1]: http://pgfoundry.org/projects/pgtune/
>> [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>> [3]:
>> http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/
>>
>> Thanks,
>> Petr Praus
>>
>> PS:
>> I also posted the question here:
>> http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-downbut a few people suggested
>>
>
>
> --
> **
>
> Marcos Luis Ortíz Valmaseda
> about.me/marcosortiz
> @marcosluis2186 <http://twitter.com/marcosluis2186>
> **
>
> <http://www.uci.cu/>
>
>

Regards, Petr.
Tuning PostgreSQL is not just change the postgresql.conf, it includes
more things like:
- the filesystem that you are using
- the kernel version that you using (particularly in Linux systems)
- the tuning to kernel variables
- the type of discs that you are using (SSDs are very fast, like you saw
in your iMac system)

On 10/30/2012 02:44 PM, Petr Praus wrote:
> I just found one particularly interesting fact: when I perform the
> same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1
> and 16GB RAM, I don't experience the slow down.
> Specifically:
> set work_mem='1MB';
> select ...; // running time is ~1800 ms
> set work_mem='96MB';
> select ...' // running time is ~1500 ms
>
> When I do exactly the same query (the one from my previous post) with
> exactly the same data on the server:
> I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.
>
> The Mac has SSD so it's understandably faster, but it exhibits a
> behavior I would expect. What am I doing wrong here?
>
> Thanks.
>
> On 30 October 2012 14:08, Petr Praus <petr(at)praus(dot)net
> <mailto:petr(at)praus(dot)net>> wrote:
>
> Hello,
>
> I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core
> machine with 16GB of RAM. The server is dedicated to this
> database, the disks are local RAID10. Given that the default
> postgresql.conf is quite conservative regarding memory settings, I
> thought it might be a good idea to allow Postgres to use more
> memory. To my surprise, following advice in the performance tuning
> guide on Postgres wiki[2] significantly slowed down practically
> every query I run but it's more noticeable on the more complex
> queries.
>
> I also tried running pgtune[1] which gave the following
> recommendation with more parameters tuned, but that didn't change
> anything. It suggests shared_buffers of 1/4 of RAM size which
> seems to in line with advice elsewhere (and on PG wiki in particular).
>
> default_statistics_target = 50
> maintenance_work_mem = 960MB
> constraint_exclusion = on
> checkpoint_completion_target = 0.9
> effective_cache_size = 11GB
> work_mem = 96MB
> wal_buffers = 8MB
> checkpoint_segments = 16
> shared_buffers = 3840MB
> max_connections = 80
>
> I tried reindexing the whole database after changing the settings
> (using REINDEX DATABASE), but that didn't help either. I played
> around with shared_buffers and work_mem. Gradually changing them
> from the very conservative default values (128k / 1MB) also
> gradually decreased performance.
>
> I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit
> seems to be that Hash Join is significantly slower. It's not clear
> to me why.
>
> To give some specific example, I have the following query. It runs
> in ~2100ms on the default configuration and ~3300ms on the
> configuration with increased buffer sizes:
>
> select count(*) from contest c
> left outer join contestparticipant cp on c.id
> <http://c.id>=cp.contestId
> left outer join teammember tm on tm.contestparticipantid=cp.id
> <http://cp.id>
> left outer join staffmember sm on cp.id
> <http://cp.id>=sm.contestparticipantid
> left outer join person p on p.id <http://p.id>=cp.personid
> left outer join personinfo pi on pi.id
> <http://pi.id>=cp.personinfoid
> where pi.lastname like '%b%' or pi.firstname like '%a%';
>
> EXPLAIN (ANALYZE,BUFFERS) for the query above:
>
> - Default buffers: http://explain.depesz.com/s/xaHJ
> - Bigger buffers: http://explain.depesz.com/s/Plk
>
> The tables don't have anything special in them
>
> The question is why am I observing decreased performance when I
> increase buffer sizes? The machine is definitely not running out
> of memory. Allocation if shared memory in OS is (`shmmax` and
> `shmall`) is set to very large values, that should not be a
> problem. I'm not getting any errors in the Postgres log either.
> I'm running autovacuum in the default configuration but I don't
> expect that has anything to do with it. All queries were run on
> the same machine few seconds apart, just with changed
> configuration (and restarted PG).
>
> I also found a blog post [3] which experiments with various
> work_mem values that run into similar behavior I'm experiencing
> but it doesn't really explain it.
>
> [1]: http://pgfoundry.org/projects/pgtune/
> [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> [3]:
> http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/
>
> Thanks,
> Petr Praus
>
> PS:
> I also posted the question here:
> http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-down
> but a few people suggested
>
>

--

Marcos Luis Ortíz Valmaseda
about.me/marcosortiz <http://about.me/marcosortiz>
@marcosluis2186 <http://twitter.com/marcosluis2186>

10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci

Am 01.11.2012 21:40, schrieb Marcos Ortiz:
> Regards, Petr.
> Tuning PostgreSQL is not just change the postgresql.conf, it includes
> more things like:
> - the filesystem that you are using
> - the kernel version that you using (particularly in Linux systems)
> - the tuning to kernel variables
> - the type of discs that you are using (SSDs are very fast, like you
> saw in your iMac system)
>
> On 10/30/2012 02:44 PM, Petr Praus wrote:
>> I just found one particularly interesting fact: when I perform the
>> same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1
>> and 16GB RAM, I don't experience the slow down.
>> Specifically:
>> set work_mem='1MB';
>> select ...; // running time is ~1800 ms
>> set work_mem='96MB';
>> select ...' // running time is ~1500 ms
>>
>> When I do exactly the same query (the one from my previous post) with
>> exactly the same data on the server:
>> I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.
>>
Just some thoughts (interested in this, once seen a Sybase ASE come
close to a halt when we threw a huge lot of SHM at it...).

8 cores, so probably on 2 sockets? What CPU generation?

Both explain outputs show an amount of "read" buffers. Did you warm the
caches before testing?

Maybe you're hitting a NUMA issue there? If those reads come from the
OS' cache, the scheduler might decide to move your process to a
different core (that can access the cache better), then moves it back
when you access the SHM segment more (the ~4GB get allocated at startup,
so probably "close" to the CPU the postmaster ist running on). A
migration to a different cacheline is very expensive.

The temp reads/writes (i.e., the OS cache for the temp files) would
probably be allocated close to the CPU requesting the temp file.

Just groping about in the dark though... but the iMac is obviously not
affected by this, with one socket/memory channel/cache line.

Might be worth to
- manually pin (with taskset) the session you test this in to a
particular CPU (once on each socket) to see if the times change
- try reducing work_mem in the session you're testing in (so you have
large SHM, but small work mem)

Cheers,

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

Two possibilities:

caching. make sure to run each query several times in a row.

zone reclaim mode. If this has gotten turned on turn it back off.

How to tell:

sysctl -n vm.zone_reclaim_mode

Output should be 0. If it's not, then add this to /etc/sysctl.conf:

vm.zone_reclaim_mode=0

and run: sudo sysctl -p

and see if that helps.

I did run each query several times, the results I posted are for ~10th run
of the query.

The zone reclaim mode is 0.

On 2 November 2012 00:39, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> Two possibilities:
>
> caching. make sure to run each query several times in a row.
>
> zone reclaim mode. If this has gotten turned on turn it back off.
>
> How to tell:
>
> sysctl -n vm.zone_reclaim_mode
>
> Output should be 0. If it's not, then add this to /etc/sysctl.conf:
>
> vm.zone_reclaim_mode=0
>
> and run: sudo sysctl -p
>
> and see if that helps.
>

On 1 November 2012 18:25, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 01.11.2012 21:40, schrieb Marcos Ortiz:
>
> Regards, Petr.
> Tuning PostgreSQL is not just change the postgresql.conf, it includes more
> things like:
> - the filesystem that you are using
> - the kernel version that you using (particularly in Linux systems)
> - the tuning to kernel variables
> - the type of discs that you are using (SSDs are very fast, like you saw
> in your iMac system)
>
> On 10/30/2012 02:44 PM, Petr Praus wrote:
>
> I just found one particularly interesting fact: when I perform the same
> test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB
> RAM, I don't experience the slow down.
> Specifically:
> set work_mem='1MB';
> select ...; // running time is ~1800 ms
> set work_mem='96MB';
> select ...' // running time is ~1500 ms
>
> When I do exactly the same query (the one from my previous post) with
> exactly the same data on the server:
> I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.
>
> Just some thoughts (interested in this, once seen a Sybase ASE come
> close to a halt when we threw a huge lot of SHM at it...).
>
> 8 cores, so probably on 2 sockets? What CPU generation?
>

The processors are two quad core Intel x7350 Xeon at 2.93Ghz. It's somewhat
older (released late 2007) but it's not absolute speed I'm after - it's the
difference in speed when increasing work_mem.

> Both explain outputs show an amount of "read" buffers. Did you warm the
> caches before testing?
>

I did warm the caches before testing.

>
> Maybe you're hitting a NUMA issue there? If those reads come from the OS'
> cache, the scheduler might decide to move your process to a different core
> (that can access the cache better), then moves it back when you access the
> SHM segment more (the ~4GB get allocated at startup, so probably "close" to
> the CPU the postmaster ist running on). A migration to a different
> cacheline is very expensive.
>
> The temp reads/writes (i.e., the OS cache for the temp files) would
> probably be allocated close to the CPU requesting the temp file.
>
> Just groping about in the dark though... but the iMac is obviously not
> affected by this, with one socket/memory channel/cache line.
>

I made a test with Ubuntu 12.04 VM machine (vmware workstation 4.1.3 on the
same iMac) with 4GB memory and shared_buffers=1GB. To my slight surprise,
the query is faster on Ubuntu VM machine then on the OSX (~1050ms vs.
~1500ms with work_mem=1MB). This might be caused
by effective_io_concurrency which is enabled on Ubuntu but can't be enabled
on OSX because postgres does not support it there. The interesting thing is
that increasing work_mem to 96MB on Ubuntu slows down the query to about
~1250ms from ~1050ms.

>
> Might be worth to
> - manually pin (with taskset) the session you test this in to a particular
> CPU (once on each socket) to see if the times change
>

I tested this and it does not seem to have any effect (assuming I used
taskset correctly but I think so: taskset 02 psql to pin down to CPU #1 and
taskset 01 psql to pin to CPU #0).

> - try reducing work_mem in the session you're testing in (so you have
> large SHM, but small work mem)
>

Did this and it indicates to me that shared_buffers setting actually does
not have an effect on this behaviour as I previously thought it has. It
really boils down to work_mem: when I set shared_buffers to something large
(say 4GB) and just play with work_mem the problem persists.

>
> Cheers,
>
> --
> Gunnar "Nick" Bluth
> RHCE/SCLA
>
> Mobil +49 172 8853339
> Email: gunnar(dot)bluth(at)pro-open(dot)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
>
>

Am 02.11.2012 17:12, schrieb Petr Praus:

Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A
process migration would be even more expensive there.

> Might be worth to
> - manually pin (with taskset) the session you test this in to a
> particular CPU (once on each socket) to see if the times change
>
>
> I tested this and it does not seem to have any effect (assuming I used
> taskset correctly but I think so: taskset 02 psql to pin down to CPU
> #1 and taskset 01 psql to pin to CPU #0).
Well, that pinned your _client_ to the CPUs, not the server side session ;-)
You'd have to spot for the PID of the new "IDLE" server process and pin
that using "taskset -p". Also, 01 and 02 are probably cores in the same
package/socket. Try "lscpu" first and spot for "NUMA node*" lines at the
bottom.
But anyway... let's try something else first:
>
> - try reducing work_mem in the session you're testing in (so you
> have large SHM, but small work mem)
>
>
> Did this and it indicates to me that shared_buffers setting actually
> does not have an effect on this behaviour as I previously thought it
> has. It really boils down to work_mem: when I set shared_buffers to
> something large (say 4GB) and just play with work_mem the problem
> persists.
This only confirms what we've seen before. As soon as your work_mem
permits an in-memory sort of the intermediate result set (which at that
point in time is where? In the SHM, or in the private memory of the
backend? I can't tell, tbth), the sort takes longer than when it's using
a temp file.

What if you reduce the shared_buffers to your original value and only
increase/decrease the session's work_mem? Same behaviour?

Cheers,

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

On 3 November 2012 05:31, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 02.11.2012 17:12, schrieb Petr Praus:
>
> Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A
> process migration would be even more expensive there.
>
> Might be worth to
>> - manually pin (with taskset) the session you test this in to a
>> particular CPU (once on each socket) to see if the times change
>>
>
> I tested this and it does not seem to have any effect (assuming I used
> taskset correctly but I think so: taskset 02 psql to pin down to CPU #1 and
> taskset 01 psql to pin to CPU #0).
>
> Well, that pinned your _client_ to the CPUs, not the server side session
> ;-)
> You'd have to spot for the PID of the new "IDLE" server process and pin
> that using "taskset -p". Also, 01 and 02 are probably cores in the same
> package/socket. Try "lscpu" first and spot for "NUMA node*" lines at the
> bottom.
>
Ah, stupid me :)

> But anyway... let's try something else first:
>
>
>
>> - try reducing work_mem in the session you're testing in (so you have
>> large SHM, but small work mem)
>>
>
> Did this and it indicates to me that shared_buffers setting actually
> does not have an effect on this behaviour as I previously thought it has.
> It really boils down to work_mem: when I set shared_buffers to something
> large (say 4GB) and just play with work_mem the problem persists.
>
> This only confirms what we've seen before. As soon as your work_mem
> permits an in-memory sort of the intermediate result set (which at that
> point in time is where? In the SHM, or in the private memory of the
> backend? I can't tell, tbth), the sort takes longer than when it's using a
> temp file.
>
> What if you reduce the shared_buffers to your original value and only
> increase/decrease the session's work_mem? Same behaviour?
>

Yes, same behaviour. I let the shared_buffers be the default (which is
8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same
times as before). It really seems that the culprit is work_mem.

>
> Cheers,
>
> --
> Gunnar "Nick" Bluth
> RHCE/SCLA
>
> Mobil +49 172 8853339
> Email: gunnar(dot)bluth(at)pro-open(dot)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
>
>

Am 03.11.2012 16:20, schrieb Petr Praus:
>
> Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA.
> A process migration would be even more expensive there.
>

Ok, I've actually looked these up now... at the time these were current,
I was in the lucky situation to only deal with Opterons. And actually,
with these CPUs it is pretty possible that Scott Marlowe's hint (check
vm.zone_reclaim_mode) was pointing in the right direction. Did you check
that?

> Yes, same behaviour. I let the shared_buffers be the default
> (which is 8MB). With work_mem 1MB the query runs fast, with 96MB
> it runs slow (same times as before). It really seems that the
> culprit is work_mem.
>
>

Well, I'm pretty sure that having more work_mem is a good thing (tm)
normally ;-)

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 03.11.2012 16:20, schrieb Petr Praus:
>
>
> Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A
>> process migration would be even more expensive there.
>>
>>
> Ok, I've actually looked these up now... at the time these were current, I
> was in the lucky situation to only deal with Opterons. And actually, with
> these CPUs it is pretty possible that Scott Marlowe's hint (check
> vm.zone_reclaim_mode) was pointing in the right direction. Did you check
> that?
>

I did check that, it's zero. I responded to his message, but my messages to
the mailing list are getting delayed by ~24 hours because somebody has to
always bless them.

>
>
> Yes, same behaviour. I let the shared_buffers be the default (which
>> is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow
>> (same times as before). It really seems that the culprit is work_mem.
>>
>
>
> Well, I'm pretty sure that having more work_mem is a good thing (tm)
> normally ;-)
>

Well, that's what I always thought too! :-)

> --
> Gunnar "Nick" Bluth
> RHCE/SCLA
>
> Mobil +49 172 8853339
> Email: gunnar(dot)bluth(at)pro-open(dot)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
>
>

Am 03.11.2012 18:19, schrieb Petr Praus:
> On 3 November 2012 12:09, Gunnar "Nick" Bluth
> <gunnar(dot)bluth(at)pro-open(dot)de <mailto:gunnar(dot)bluth(at)pro-open(dot)de>> wrote:
>
> Am 03.11.2012 16:20, schrieb Petr Praus:
>>
>> Your CPUs are indeed pretty oldschool. FSB based, IIRC, not
>> NUMA. A process migration would be even more expensive there.
>>
>
> Ok, I've actually looked these up now... at the time these were
> current, I was in the lucky situation to only deal with Opterons.
> And actually, with these CPUs it is pretty possible that Scott
> Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the
> right direction. Did you check that?
>
>
> I did check that, it's zero. I responded to his message, but my
> messages to the mailing list are getting delayed by ~24 hours because
> somebody has to always bless them.
>
>
>
>> Yes, same behaviour. I let the shared_buffers be the default
>> (which is 8MB). With work_mem 1MB the query runs fast, with
>> 96MB it runs slow (same times as before). It really seems
>> that the culprit is work_mem.
>>
>>
>
> Well, I'm pretty sure that having more work_mem is a good thing
> (tm) normally ;-)
>
>
> Well, that's what I always thought too! :-)
>
So, to sum this up (and make someone more competent bite on it maybe
;-), on your SMP, FSB, "fake-multicore" system all "hash"-related works
that potentially switch to different implementations internally (but
w/out telling us so) when given more work_mem are slower.

I'm pretty sure you're hitting some subtle, memory-access-related
cornercase here.

The L2 cache of your X7350 CPUs is 2MB, could you run the tests with,
say, 1, 2, 4 and 8MB of work_mem and post the results?

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

On Sat, Nov 3, 2012 at 10:09 AM, Gunnar "Nick" Bluth
<gunnar(dot)bluth(at)pro-open(dot)de> wrote:

> Well, I'm pretty sure that having more work_mem is a good thing (tm)
> normally ;-)

In my experience when doing sorts in isolation, having more work_mem
is a bad thing, unless it enables you to remove a layer of
tape-merging. I always blamed it on the L1/L2 etc. levels of caching.

Cheers,

Jeff

On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>> normally ;-)
>
> In my experience when doing sorts in isolation, having more work_mem
> is a bad thing, unless it enables you to remove a layer of
> tape-merging. I always blamed it on the L1/L2 etc. levels of caching.

Blame it on quicksort, which is quite cache-unfriendly.

Perhaps PG should consider using in-memory mergesort for the bigger chunks.

On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>>> normally ;-)
>>
>> In my experience when doing sorts in isolation, having more work_mem
>> is a bad thing, unless it enables you to remove a layer of
>> tape-merging. I always blamed it on the L1/L2 etc. levels of caching.
>
> Blame it on quicksort, which is quite cache-unfriendly.

The observation applies to heap sort. If you can't set work_mem large
enough to do the sort in memory, then you want to set it just barely
large enough to avoid two layers of tape sorting. Any larger than
that reduces performance rather than increasing it. Of course that
assumes you have the luxury of knowing ahead of time exactly how large
your sort will be and can set work_mem accordingly on a case by case
basis, which is unlikely in the real world.

> Perhaps PG should consider using in-memory mergesort for the bigger chunks.

Cheers,

Jeff

On Mon, Nov 5, 2012 at 2:09 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> In my experience when doing sorts in isolation, having more work_mem
>>> is a bad thing, unless it enables you to remove a layer of
>>> tape-merging. I always blamed it on the L1/L2 etc. levels of caching.
>>
>> Blame it on quicksort, which is quite cache-unfriendly.
>
> The observation applies to heap sort.

Well, heapsort is worse, but quicksort is also quite bad.

On Mon, Nov 5, 2012 at 2:40 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Mon, Nov 5, 2012 at 2:09 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>> In my experience when doing sorts in isolation, having more work_mem
>>>> is a bad thing, unless it enables you to remove a layer of
>>>> tape-merging. I always blamed it on the L1/L2 etc. levels of caching.
>>>
>>> Blame it on quicksort, which is quite cache-unfriendly.
>>
>> The observation applies to heap sort.
>
> Well, heapsort is worse, but quicksort is also quite bad.

Here[0], an interesting analysis. I really believe quicksort in PG
(due to its more complex datatypes) fares a lot worse.

[0] http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CD0QFjAB&url=http%3A%2F%2Fwww.cs.auckland.ac.nz%2F~mcw%2FTeaching%2Frefs%2Fsorting%2Fladner-lamarca-cach-sorting.pdf&ei=PPqXUMnEL9PaqQHntoDgDQ&usg=AFQjCNE3mDf6ydj1MHUzfQw13TccOa895A

On 4 November 2012 02:48, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 03.11.2012 18:19, schrieb Petr Praus:
>
> On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:
>
>> Am 03.11.2012 16:20, schrieb Petr Praus:
>>
>>
>> Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A
>>> process migration would be even more expensive there.
>>>
>>>
>> Ok, I've actually looked these up now... at the time these were current,
>> I was in the lucky situation to only deal with Opterons. And actually, with
>> these CPUs it is pretty possible that Scott Marlowe's hint (check
>> vm.zone_reclaim_mode) was pointing in the right direction. Did you check
>> that?
>>
>
> I did check that, it's zero. I responded to his message, but my messages
> to the mailing list are getting delayed by ~24 hours because somebody has
> to always bless them.
>
>
>>
>>
>> Yes, same behaviour. I let the shared_buffers be the default (which
>>> is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow
>>> (same times as before). It really seems that the culprit is work_mem.
>>>
>>
>>
>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>> normally ;-)
>>
>
> Well, that's what I always thought too! :-)
>
>
> So, to sum this up (and make someone more competent bite on it maybe
> ;-), on your SMP, FSB, "fake-multicore" system all "hash"-related works
> that potentially switch to different implementations internally (but w/out
> telling us so) when given more work_mem are slower.
>
Yes, but note that this happens only in Linux. Increasing work_mem on my
iMac increases performance (but the queries are slower under OSX than on
virtualized Ubuntu on the same machine). Over the weekend, I tried the same
test on my Ubuntu home machine with Ivy Bridge i5 3570K and it also slows
down (from ~900ms with work_mem=1MB to ~1200ms with work_mem=96MB).

>
> I'm pretty sure you're hitting some subtle, memory-access-related
> cornercase here.
>
> The L2 cache of your X7350 CPUs is 2MB, could you run the tests with, say,
> 1, 2, 4 and 8MB of work_mem and post the results?
>
I made a pgbench test with the same query and run it 25 times (5 clients, 5
transactions each):
work_mem speed
1MB 1794ms
2MB 1877ms
4MB 2084ms
8MB 2141ms
10MB 2124ms
12MB 3018ms
16MB 3004ms
32MB 2999ms
64MB 3015ms

It seems that there is some sort of "plateau".

>
> --
> Gunnar "Nick" Bluth
> RHCE/SCLA
>
> Mobil +49 172 8853339
> Email: gunnar(dot)bluth(at)pro-open(dot)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
>
>

Am 05.11.2012 18:09, schrieb Jeff Janes:
> On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>>>> normally ;-)
>>> In my experience when doing sorts in isolation, having more work_mem
>>> is a bad thing, unless it enables you to remove a layer of
>>> tape-merging. I always blamed it on the L1/L2 etc. levels of caching.
>> Blame it on quicksort, which is quite cache-unfriendly.
> The observation applies to heap sort. If you can't set work_mem large
> enough to do the sort in memory, then you want to set it just barely
> large enough to avoid two layers of tape sorting. Any larger than
> that reduces performance rather than increasing it. Of course that
> assumes you have the luxury of knowing ahead of time exactly how large
> your sort will be and can set work_mem accordingly on a case by case
> basis, which is unlikely in the real world.
>
>> Perhaps PG should consider using in-memory mergesort for the bigger chunks.
I don't want to be the party pooper here, but when you have another look
at the EXPLAINs, you'll realize that there's not a single sort involved.
The expensive parts are HASH, HASH JOIN and HASH RIGHT JOIN (although
the SeqScan takes longer as well, for whatever reason). In those parts,
the difference is clearly in the # of buckets and batches. So to a
degree, PG even does tell us that it uses a different code path (sorry,
PG ;-)...

Greg Smith mentions an optimization wrt. Hash Joins that can become a
pitfall. His advise is to increase the statistic targets on the hashed
outer relation. Might be worth a try.

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

Am 06.11.2012 18:38, schrieb Petr Praus:
>
> Yes, but note that this happens only in Linux. Increasing work_mem on
> my iMac increases performance (but the queries are slower under OSX
> than on virtualized Ubuntu on the same machine). Over the weekend, I
> tried the same test on my Ubuntu home machine with Ivy Bridge i5 3570K
> and it also slows down (from ~900ms with work_mem=1MB to ~1200ms with
> work_mem=96MB).

OS X is rather different from a memory access point of view, IIRC. So
the direct comparison actually only shows how well the Linux FS cache
works (for the temp files created with small work_mem ;-).

The i5 puzzles me a bit though...

>
> I'm pretty sure you're hitting some subtle, memory-access-related
> cornercase here.
>
> The L2 cache of your X7350 CPUs is 2MB, could you run the tests
> with, say, 1, 2, 4 and 8MB of work_mem and post the results?
>
> I made a pgbench test with the same query and run it 25 times (5
> clients, 5 transactions each):
> work_mem speed
> 1MB 1794ms
> 2MB 1877ms
> 4MB 2084ms
> 8MB 2141ms
> 10MB 2124ms
> 12MB 3018ms
> 16MB 3004ms
> 32MB 2999ms
> 64MB 3015ms
>
> It seems that there is some sort of "plateau".
Two, afaics. The 1->2 change hints towards occasionally breaching your
L2 cache, so it can probably be ignored. The actual plateaus thus seem
to be 0-2, 2-12, >= 12.
It'd be interesting to see the EXPLAIN ANALYSE outputs for these levels,
the buckets and batches in particular. I'd reckon we'll see significant
changes at 2->4 and 10->12MB work_mem.

> So, to sum this up (and make someone more competent bite on it maybe
> ;-), on your SMP, FSB, "fake-multicore" system all "hash"-related
> works that potentially switch to different implementations internally
> (but w/out telling us so) when given more work_mem are slower.
See other post... it actually does tell us (# of buckets/batches).
However, the result is not good and could potentially be improved be
twealing the statistic_targets of the joined tables/columns.

I wonder why noone actually understanding the implementation chipped in
yet... Andres, Greg, Tom, whoever actually understands what's happening
here, anyone reading this? ;-)

Cheers,

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

On 6 November 2012 13:38, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 06.11.2012 18:38, schrieb Petr Praus:
>
>
> Yes, but note that this happens only in Linux. Increasing work_mem on my
> iMac increases performance (but the queries are slower under OSX than on
> virtualized Ubuntu on the same machine). Over the weekend, I tried the same
> test on my Ubuntu home machine with Ivy Bridge i5 3570K and it also slows
> down (from ~900ms with work_mem=1MB to ~1200ms with work_mem=96MB).
>
>
> OS X is rather different from a memory access point of view, IIRC. So the
> direct comparison actually only shows how well the Linux FS cache works
> (for the temp files created with small work_mem ;-).
>
> The i5 puzzles me a bit though...
>
>
>
>> I'm pretty sure you're hitting some subtle, memory-access-related
>> cornercase here.
>>
>> The L2 cache of your X7350 CPUs is 2MB, could you run the tests with,
>> say, 1, 2, 4 and 8MB of work_mem and post the results?
>>
> I made a pgbench test with the same query and run it 25 times (5 clients,
> 5 transactions each):
> work_mem speed
> 1MB 1794ms
> 2MB 1877ms
> 4MB 2084ms
> 8MB 2141ms
> 10MB 2124ms
> 12MB 3018ms
> 16MB 3004ms
> 32MB 2999ms
> 64MB 3015ms
>
> It seems that there is some sort of "plateau".
>
> Two, afaics. The 1->2 change hints towards occasionally breaching your L2
> cache, so it can probably be ignored. The actual plateaus thus seem to be
> 0-2, 2-12, >= 12.
> It'd be interesting to see the EXPLAIN ANALYSE outputs for these levels,
> the buckets and batches in particular. I'd reckon we'll see significant
> changes at 2->4 and 10->12MB work_mem.
>

Here are the explains, I run the query a few times before actually taking
the explain to warm up the caches. (I also noticed that explain slows down
the query execution which is probably to be expected.)

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

So, to sum this up (and make someone more competent bite on it maybe ;-),
> on your SMP, FSB, "fake-multicore" system all "hash"-related works that
> potentially switch to different implementations internally (but w/out
> telling us so) when given more work_mem are slower.
>
> See other post... it actually does tell us (# of buckets/batches).
> However, the result is not good and could potentially be improved be
> twealing the statistic_targets of the joined tables/columns.
>
> I wonder why noone actually understanding the implementation chipped in
> yet... Andres, Greg, Tom, whoever actually understands what's happening
> here, anyone reading this? ;-)
>
> Cheers,
>
> --
> Gunnar "Nick" Bluth
> RHCE/SCLA
>
> Mobil +49 172 8853339
> Email: gunnar(dot)bluth(at)pro-open(dot)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
>
>

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...

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar(dot)bluth(at)pro-open(dot)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

On 6 November 2012 14:17, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

> Am 06.11.2012 21:08, schrieb Petr Praus:
>
>
>> 2MB: http://explain.depesz.com/s/**ul1 <http://explain.depesz.com/s/ul1>
>> 4MB: http://explain.depesz.com/s/**IlVu<http://explain.depesz.com/s/IlVu>
>> 10MB: http://explain.depesz.com/s/**afx3<http://explain.depesz.com/s/afx3>
>> 12MB: http://explain.depesz.com/s/**i0vQ<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(dot)bluth(at)pro-open(dot)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
>
>

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

On 6 November 2012 14:50, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>wrote:

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

Yes, that's what I'm running now in production :) When I have more time I
may come up with more queries to test overall system better.
We'll see if anyone else comes up with something but I am out of things to
try, too. So I guess I'll put this sideways for now.

>
>
> --
> Gunnar "Nick" Bluth
> RHCE/SCLA
>
> Mobil +49 172 8853339
> Email: gunnar(dot)bluth(at)pro-open(dot)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
>
>

On 2012-10-30 14:08:56 -0500, Petr Praus wrote:
> select count(*) from contest c
> left outer join contestparticipant cp on c.id=cp.contestId
> left outer join teammember tm on tm.contestparticipantid=cp.id
> left outer join staffmember sm on cp.id=sm.contestparticipantid
> left outer join person p on p.id=cp.personid
> left outer join personinfo pi on pi.id=cp.personinfoid
> where pi.lastname like '%b%' or pi.firstname like '%a%';

Btw, not really related to the question, but the way you use left joins
here doesn't really make sense and does lead to inferior plans.
As you restrict on 'pi', the rightmost table in a chain of left joins,
there is no point in all those left joins. I would guess the overall
plan is better if use straight joins.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services