pg_stat_statements

Hello,

Postgres 8.4 has pg_stat_user_functions view to track number of calls of
stored functions and time spent in them. Then, I'm thinking a "sql statement"
version of similar view -- pg_stat_statements.

Prepared statements and statements using extended protocol are grouped
by their sql strings without parameters, that is the just same as
pg_stat_user_functions. We could ignore simple queries with parameters
because they have different expression for each execution.

We can write sql statements in server logs and gather them using some tools
(pgfouine and pqa) even now, but statement logging has unignorable overhead.
Lightweight view is useful for typical users who are only interedted in
aggregated results.

One issue is how and where to store sql strings. We could use hash values
of statement strings as short identifiers, but we need to store sql strings
somewhere to compare the IDs and original statements.

1. Store SQLs in shared memory
We need to allocate fixed region on starting servers. Should we have
another memory setting into postgresql.conf?

2. Store SQLs in stats collector process's memory
We can use dynamically allocated memory, but sending sql statements to
stat collector process is probably slow and stat file will be large.

I'm not sure which is better. It might have relevance to discussion of
shared prepared statements.

Another issue is that we could implement the feature as an add-on,
not a core feature. We can use general hooks for this purpose; We store
sql statement and their hash values in planner_hook, and record number
of execution and time in new executor begin/end hooks or by adding
a "stop-watch" executor node. Should this feature be in the core or not?
For example, dynamic shared memory allocation might be need before we move
the feature in the core.

Comments and suggestions welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center