#definePGSS_DUMP_FILE"global/pg_stat_statements.stat"

staticconstuint32PGSS_FILE_HEADER=0x20081202;

staticconstuint32PGSS_FILE_HEADER=0x20100108;

#defineUSAGE_EXEC(duration)(1.0)

{

int64calls;/* # of times executed */

doubletotal_time;/* total execution time in seconds */

int64rows;/* total # of retrieved or affected rows */

doubleusage;/* usage factor */

int64calls;/* # of times executed */

doubletotal_time;/* total execution time in seconds */

int64rows;/* total # of retrieved or affected rows */

int64shared_blks_hit;/* # of shared buffer hits */

int64shared_blks_read;/* # of shared disk blocks read */

int64shared_blks_written;/* # of shared disk blocks written */

int64local_blks_hit;/* # of local buffer hits */

int64local_blks_read;/* # of local disk blocks read */

int64local_blks_written;/* # of local disk blocks written */

int64temp_blks_read;/* # of temp blocks read */

int64temp_blks_written;/* # of temp blocks written */

doubleusage;/* usage factor */

}Counters;

}PGSSTrackLevel;

staticconststructconfig_enum_entrytrack_options[]= {

staticconststructconfig_enum_entrytrack_options[]=

{

{"none",PGSS_TRACK_NONE, false},

{"top",PGSS_TRACK_TOP, false},

{"all",PGSS_TRACK_ALL, false},

DestReceiver*dest,char*completionTag);

staticuint32pgss_hash_fn(constvoid*key,Sizekeysize);

staticintpgss_match_fn(constvoid*key1,constvoid*key2,Sizekeysize);

staticvoidpgss_store(constchar*query,doubletotal_time,uint64rows);

staticvoidpgss_store(constchar*query,doubletotal_time,uint64rows,

constBufferUsage*bufusage);

staticSizepgss_memsize(void);

staticpgssEntry*entry_alloc(pgssHashKey*key);

staticvoidentry_dealloc(void);

pgss_store(queryDesc->sourceText,

queryDesc->totaltime->total,

queryDesc->estate->es_processed);

queryDesc->estate->es_processed,

&queryDesc->totaltime->bufusage);

}

if (prev_ExecutorEnd)

instr_timestart;

instr_timeduration;

uint64rows=0;

BufferUsagebufusage;

bufusage=pgBufferUsage;

INSTR_TIME_SET_CURRENT(start);

nested_level++;

sscanf(completionTag,"COPY "UINT64_FORMAT,&rows)!=1)

rows=0;

pgss_store(queryString,INSTR_TIME_GET_DOUBLE(duration),rows);

bufusage.shared_blks_hit=

pgBufferUsage.shared_blks_hit-bufusage.shared_blks_hit;

bufusage.shared_blks_read=

pgBufferUsage.shared_blks_read-bufusage.shared_blks_read;

bufusage.shared_blks_written=

pgBufferUsage.shared_blks_written-bufusage.shared_blks_written;

bufusage.local_blks_hit=

pgBufferUsage.local_blks_hit-bufusage.local_blks_hit;

bufusage.local_blks_read=

pgBufferUsage.local_blks_read-bufusage.local_blks_read;

bufusage.local_blks_written=

pgBufferUsage.local_blks_written-bufusage.local_blks_written;

bufusage.temp_blks_read=

pgBufferUsage.temp_blks_read-bufusage.temp_blks_read;

bufusage.temp_blks_written=

pgBufferUsage.temp_blks_written-bufusage.temp_blks_written;

pgss_store(queryString,INSTR_TIME_GET_DOUBLE(duration),rows,

&bufusage);

}

{

pgss_store(constchar*query,doubletotal_time,uint64rows)

pgss_store(constchar*query,doubletotal_time,uint64rows,

constBufferUsage*bufusage)

{

pgssHashKeykey;

doubleusage;

e->counters.calls+=1;

e->counters.total_time+=total_time;

e->counters.rows+=rows;

e->counters.shared_blks_hit+=bufusage->shared_blks_hit;

e->counters.shared_blks_read+=bufusage->shared_blks_read;

e->counters.shared_blks_written+=bufusage->shared_blks_written;

e->counters.local_blks_hit+=bufusage->local_blks_hit;

e->counters.local_blks_read+=bufusage->local_blks_read;

e->counters.local_blks_written+=bufusage->local_blks_written;

e->counters.temp_blks_read+=bufusage->temp_blks_read;

e->counters.temp_blks_written+=bufusage->temp_blks_written;

e->counters.usage+=usage;

SpinLockRelease(&e->mutex);

}

PG_RETURN_VOID();

}

errmsg("materialize mode required, but it is not " \

"allowed in this context")));

if (get_call_result_type(fcinfo,NULL,&tupdesc)!=TYPEFUNC_COMPOSITE)

elog(ERROR,"return type must be a row type");

per_query_ctx=rsinfo->econtext->ecxt_per_query_memory;

oldcontext=MemoryContextSwitchTo(per_query_ctx);

tupdesc=CreateTemplateTupleDesc(PG_STAT_STATEMENTS_COLS, false);

TupleDescInitEntry(tupdesc, (AttrNumber)1,"userid",

OIDOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)2,"dbid",

OIDOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)3,"query",

TEXTOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)4,"calls",

INT8OID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)5,"total_time",

FLOAT8OID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)6,"rows",

INT8OID,-1,0);

tupstore=tuplestore_begin_heap(true, false,work_mem);

rsinfo->returnMode=SFRM_Materialize;

rsinfo->setResult=tupstore;

values[i++]=Int64GetDatumFast(tmp.calls);

values[i++]=Float8GetDatumFast(tmp.total_time);

values[i++]=Int64GetDatumFast(tmp.rows);

values[i++]=Int64GetDatumFast(tmp.shared_blks_hit);

values[i++]=Int64GetDatumFast(tmp.shared_blks_read);

values[i++]=Int64GetDatumFast(tmp.shared_blks_written);

values[i++]=Int64GetDatumFast(tmp.local_blks_hit);

values[i++]=Int64GetDatumFast(tmp.local_blks_read);

values[i++]=Int64GetDatumFast(tmp.local_blks_written);

values[i++]=Int64GetDatumFast(tmp.temp_blks_read);

values[i++]=Int64GetDatumFast(tmp.temp_blks_written);

Assert(i==PG_STAT_STATEMENTS_COLS);

/* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.sql.in,v 1.1 2009/01/04 22:19:59 tgl Exp $ */

/* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.sql.in,v 1.2 2010/01/08 00:38:19 itagaki Exp $ */

-- Adjust this setting to control where the objects get created.

SET search_path = public;

OUT query text,

OUT calls int8,

OUT total_time float8,

OUT rows int8

OUT rows int8,

OUT shared_blks_hit int8,

OUT shared_blks_read int8,

OUT shared_blks_written int8,

OUT local_blks_hit int8,

OUT local_blks_read int8,

OUT local_blks_written int8,

OUT temp_blks_read int8,

OUT temp_blks_written int8

)

RETURNS SETOF record

AS 'MODULE_PATHNAME'

<!-- $PostgreSQL: pgsql/doc/src/sgml/pgstatstatements.sgml,v 1.5 2009/12/15 20:04:49 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/pgstatstatements.sgml,v 1.6 2010/01/08 00:38:20 itagaki Exp $ -->

<sect1 id="pgstatstatements">

<title>pg_stat_statements</title>

<entry>Total number of rows retrieved or affected by the statement</entry>

</row>

<row>

<entry><structfield>shared_blks_hit</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of shared blocks hits by the statement</entry>

</row>

<row>

<entry><structfield>shared_blks_read</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of shared blocks reads by the statement</entry>

</row>

<row>

<entry><structfield>shared_blks_written</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of shared blocks writes by the statement</entry>

</row>

<row>

<entry><structfield>local_blks_hit</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of local blocks hits by the statement</entry>

</row>

<row>

<entry><structfield>local_blks_read</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of local blocks reads by the statement</entry>

</row>

<row>

<entry><structfield>local_blks_written</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of local blocks writes by the statement</entry>

</row>

<row>

<entry><structfield>temp_blks_read</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of temp blocks reads by the statement</entry>

</row>

<row>

<entry><structfield>temp_blks_written</structfield></entry>

<entry><type>bigint</type></entry>

<entry></entry>

<entry>Total number of temp blocks writes by the statement</entry>

</row>

</tbody>

</tgroup>

</table>

<title>Sample output</title>

<programlisting>

$ pgbench -i bench

postgres=# SELECT pg_stat_statements_reset();

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench

$ pgbench -c10 -t300 -M prepared bench

postgres=# \x

postgres=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;

-[ RECORD 1 ]------------------------------------------------------------

userid | 10

dbid | 63781

query | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2;

calls | 3000

total_time | 20.716706

rows | 3000

-[ RECORD 2 ]------------------------------------------------------------

userid | 10

dbid | 63781

query | UPDATE tellers SET tbalance = tbalance + $1 WHERE tid = $2;

calls | 3000

total_time | 17.1107649999999

rows | 3000

-[ RECORD 3 ]------------------------------------------------------------

userid | 10

dbid | 63781

query | UPDATE accounts SET abalance = abalance + $1 WHERE aid = $2;

calls | 3000

total_time | 0.645601

rows | 3000

bench=# \x

bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /

nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent

FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

-[ RECORD 1 ]---------------------------------------------------------------------

query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;

calls | 3000

total_time | 9.60900100000002

rows | 2836

hit_percent | 99.9778970000200936

-[ RECORD 2 ]---------------------------------------------------------------------

query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;

calls | 3000

total_time | 8.015156

rows | 2990

hit_percent | 99.9731126579631345

-[ RECORD 3 ]---------------------------------------------------------------------

query | copy pgbench_accounts from stdin

calls | 1

total_time | 0.310624

rows | 100000

hit_percent | 0.30395136778115501520

-[ RECORD 4 ]---------------------------------------------------------------------

query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;

calls | 3000

total_time | 0.271741999999997

rows | 3000

hit_percent | 93.7968855088209426

-[ RECORD 5 ]---------------------------------------------------------------------

query | alter table pgbench_accounts add primary key (aid)

calls | 1

total_time | 0.08142

rows | 0

hit_percent | 34.4947735191637631

</programlisting>

</sect2>