Hello all,

I'm now working on performance tuning for PostgreSQL application.

I know shared_buffers and sort_mem have huge impacts for the performance.

If a disk sort (called tape sort in the code) is occured, we need to
increase sort_mem value. Then I found it is difficult to get a
reasonable value for sort_mem.

So I've implemented new five functions. These functions can give some
hints to estimate the sort_mem value.

 - pg_stat_get_heap_all_sorts()
 - pg_stat_get_heap_tape_sorts()
 - pg_stat_get_index_all_sorts()
 - pg_stat_get_index_tape_sorts()
 - pg_stat_get_max_sort_size()

Using these functions, we can create a new system view about sort memory
condition and statistics as below.
------------------------------------------------------------------
snaga=# select pg_stat_get_heap_all_sorts() as heap_all,
               pg_stat_get_heap_tape_sorts() as heap_tape,
               pg_stat_get_index_all_sorts() as index_all,
               pg_stat_get_index_tape_sorts() as index_tape,
               pg_stat_get_max_sort_size() as max_sort_size;
 heap_all | heap_tape | index_all | index_tape | max_sort_size
----------+-----------+-----------+------------+---------------
        2 |         1 |         0 |          0 |     110203384
(1 row)

snaga=#
------------------------------------------------------------------

And my patch reports sort memory condition to the log.

------------------------------------------------------------------
> NOTICE:  tuplesort is attempting to use physical device.
> NOTICE:  Max used size of the sort memory (213109 kB)
------------------------------------------------------------------

I'm ready to post this patch.

Is this useful?  Any comments?

-- 
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/


Tue, 14 Sep 2004 15:07:02 +0900
Satoshi Nagayasu <nagayasus@nttdata.co.jp>
sort statistics and functions