The following is was what I noticed when comparing particular postgresql configuration parameters for an application I currently use.
This is for a particular select query which is heavily used. It is being run against a 3 column 10 million row table with a non unique index on one of the int4 columns. The query returns (123910 rows). All times are averages of several attempts at running the query and no other activity except that to run the OS was taking place at the time. Where the best and worst times differ by a noticeable amount I have added a second line to the row.
shared_buffers effective_cache
1024 200 Time: 843.300 ms
8192 200 Time: 793.345 ms
16384 200 Time: 813.399 ms
32768 200 Time: 360.111 ms Time: 401.952 ms
65536 200 Time: 379.023 ms Time: 439.706 ms
131072 200 Time: 387.866 ms Time: 404.930 ms
262144 200 Time: 362.229 ms Time: 384.344 ms
327680 200 Time: 359.975 ms Time: 383.517 ms
shared_buffers effective_cache
1024 1024 Time: 840.544 ms
1024 8192 Time: 837.187 ms
1024 16384 Time: 829.379 ms
1024 32768 Time: 826.749 ms
1024 65536 Time: 824.526 ms
1024 131072 Time: 808.480 ms
1024 262144 Time: 825.258 ms
1024 327680 Time: 817.206 ms
We can see here that the shared_buffers has the largest single affect on performance for this select statement. Would increaseing the effective cache setting while using the optimum shared bufffers chage anything?
shared_buffers effective_cache
32768 1024 Time: 361.477 ms Time: 364.341 ms
32768 8192 Time: 383.347 ms Time: 388.539 ms
32768 16384 Time: 422.892 ms Time: 425.068 ms
32768 32768 Time: 356.700 ms Time: 387.811 ms
32768 65536 Time: 360.410 ms Time: 406.472 ms
32768 131072 Time: 423.960 ms Time: 425.333 ms
32768 262144 Time: 386.507 ms Time: 392.272 ms
32768 327680 Time: 383.629 ms Time: 385.097 ms
It is quite plain from above that the most important parameter between shared_buffers and effective_cache for my select query is definitely shared_buffers.
Adding “order by column” and testing
sort_mem
1024 Time: 640.896 ms
8192 Time: 582.495 ms
16384 Time: 505.777 ms
32768 Time: 505.784 ms
We can see here that increasing the sort_mem has a good effect on performance.
Now adding a simple limit clause to the sorted results gives me another boost to the procedure.
limit
50000 Time: 391.164 ms
25000 Time: 350.564 ms
10000 Time: 325.975 ms
1000 Time: 311.234 ms
1 Time: 309.303 ms
We can see here that preparing the results has a large impact in performance. Something else that a lot of people miss is the order the columns are in, in the query used.
For instance asking for the colums in the order they are in in the table we get
Time: 360.379 ms
If we reverse the order of three columns we get.
Time: 405.506 ms
This is a big difference if the query is called a lot.