Re: Questions on query planner, join types, and work_mem

Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> What I do to quantify that is...well, the attached shows it better
> than I can describe; only works on 9.0 or later as it depends on a
> feature I added for this purpose there. It measures exactly how
> much buffer cache churn happened during a test, in this case
> creating a pgbench database.

I'm not entirely sure I understand what I'm supposed to get from
that. On a 3GB workstation, a compile from a recent HEAD checkout,
with a default postgresql.conf file, I get this:

-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:25:46.683766-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 0
buffers_alloc | 73

Initializing pgbench
-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:27:49.062551-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 633866
buffers_alloc | 832

I boost shared_buffers from 32MB to 320MB, restart, and get this:

-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:30:42.816719-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 0
buffers_alloc | 0

Initializing pgbench
-[ RECORD 1 ]------+------------------------------
now | 2010-08-04 14:32:40.750098-05
checkpoints_timed | 0
checkpoints_req | 0
buffers_checkpoint | 0
buffers_clean | 0
maxwritten_clean | 0
buffers_backend | 630794
buffers_alloc | 2523

So run time dropped from 123 seconds to 118 seconds, buffers_backend
dropped by less than 0.5%, and buffers_alloc went up. Assuming this
is real, and not just "in the noise" -- what conclusions would you
draw from this? Dedicating an additional 10% of my free memory got
me a 4% speed improvement? Was I supposed to try with other scales?
Which ones?

-Kevin