From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_stat_statements
Date: 2008-06-13 08:33:18
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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.

ITAGAKI Takahiro
NTT Open Source Software Center


Browse pgsql-hackers by date

  From Date Subject
Next Message billy 2008-06-13 08:39:59 a problem when poring from Oracle's PL/SQL to PLPGSQL
Previous Message Zdenek Kotala 2008-06-13 08:27:19 Re: Proposal: Multiversion page api (inplace upgrade)