Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com> writes:
> This is a new statistics view that is used to provide the number of
> SQL operations that are
> happened on a particular interval of time. This view is useful for the
> system to find out the
> pattern of the operations that are happening in the instance during
> particular interval of
> time.

> Following is the more or less columns and their details of the pg_stat_sql view.

> postgres=# \d pg_stat_sql
> View "pg_catalog.pg_stat_sql"
> Column | Type | Modifiers
> -------------+--------------------------+-----------
> selects | bigint |
> inserts | bigint |
> deletes | bigint |
> updates | bigint |
> declares | bigint |
> fetches | bigint |
> copies | bigint |
> reindexes | bigint |
> truncates | bigint |
> stats_reset | timestamp with time zone |

1. This set of counters seems remarkably random. Why, for instance,
count reindexes but not original index creations?

2. What will you do with cases such as SELECTs containing modifying CTEs?
Or EXPLAIN ANALYZE? Does CLUSTER count as a REINDEX? Does REFRESH
MATERIALIZED VIEW count as a SELECT? (Or maybe it's an INSERT?)

If you're going to be selective about what you count, you're forever
going to be fielding complaints from users who are unhappy that you
didn't count some statement type they care about, or feel that you
misclassified some complex case.

I'm inclined to suggest you forget this approach and propose a single
counter for "SQL commands executed", which avoids all of the above
definitional problems. People who need more detail than that are
probably best advised to look to contrib/pg_stat_statements, anyway.

Also, if you do that, there hardly seems a need for a whole new view.
You could just add a column to pg_stat_database. The incremental
maintenance effort doesn't seem enough to justify its own GUC, either.

regards, tom lane