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(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/