sort statistics and functions

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/