Re: New SQL counter statistics view (pg_stat_sql)

On Fri, Sep 2, 2016 at 2:33 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:
> On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
>> 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.

I think it is not a good idea to make the command names used here the
plural forms of the command tags.  Instead of "inserts", "updates",
"imports", etc. just use "INSERT", "UPDATE", "IMPORT".  That's simpler
and less error prone - e.g. you won't end up with things like
"refreshs", which is not a word.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company