Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

From: Petr Praus <petr(at)praus(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Date: 2012-10-30 19:08:56
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-performance


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

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

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
left outer join teammember tm on
left outer join staffmember sm on
left outer join person p on
left outer join personinfo pi on
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) for the query above:

- Default buffers:
- Bigger buffers:

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.


Petr Praus

I also posted the question here:
a few people suggested


Browse pgsql-performance by date

  From Date Subject
Next Message Petr Praus 2012-10-30 19:44:53 Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Previous Message Josh Berkus 2012-10-30 19:07:37 Re: High %SYS CPU usage