On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <alvhe...@2ndquadrant.com>
> Haribabu Kommi wrote:
>> Apart from the above, here are the following list of command tags that
>> are generated in the code, I took only the first word of the command tag
>> just to see how many categories present. The number indicates the
>> subset of operations or number of types it is used. Like create table,
>> create function and etc.
> Sounds about right.  I suppose all those cases that you aggregated here
> would expand to full tags in the actual code.  I furthermore suppose
> that some of these could be ignored, such as the transaction ones and
> things like load, lock, move, fetch, discard, deallocate (maybe lump
> them all together under "other", or some other rough categorization, as
> Tom suggests).

Following is the pg_stat_sql view with the SQL categories that I considered
that are important. Rest of the them will be shown under others category.

postgres=# \d pg_stat_sql
             View "pg_catalog.pg_stat_sql"
     Column      |           Type           | Modifiers
 inserts         | bigint                   |
 deletes         | bigint                   |
 updates         | bigint                   |
 selects         | bigint                   |
 declare_cursors | bigint                   |
 closes          | bigint                   |
 creates         | bigint                   |
 drops           | bigint                   |
 alters          | bigint                   |
 imports         | bigint                   |
 truncates       | bigint                   |
 copies          | bigint                   |
 grants          | bigint                   |
 revokes         | bigint                   |
 clusters        | bigint                   |
 vacuums         | bigint                   |
 analyzes        | bigint                   |
 refreshs        | bigint                   |
 locks           | bigint                   |
 checkpoints     | bigint                   |
 reindexes       | bigint                   |
 deallocates     | bigint                   |
 others          | bigint                   |
 stats_reset     | timestamp with time zone |

If any additions/deletions, I can accommodate them.

The stats data gets updated in exec_simple_query and exec_execute_message
functions and the collected stats will be sent to stats collector similar
like function usage stats in pgstat_report_stat function.

These SQL statistics data is stored in the stats file similar like global
statistics. The STAT file format is updated to accommodate the new stats.

A new GUC "track_sql" is added to track the SQL statement
statistics, by default this is off. Only superuser can change this

Attached a patch for the same.

>  Also, for many of these commands it's probably relevant
> whether they are acting on a temporary object or not; we should either
> count these separately, or not count the temp ones at all.

Currently the SQL stats are not checking any object level that is a temp
one or not? The temp objects are specific to a backend only. But what
I feel, any way that is an SQL query that was executed on a temp object,
so we need to count that operation.

I feel this SQL stats should not worry about the object type. May be I am

Hari Babu
Fujitsu Australia

Attachment: pg_stat_sql_1.patch
Description: Binary data

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to