On 24 May 2012 11:50, Magnus Hagander <magnus@hagander.net> wrote:
> Was there any actual reason why we didn't end up exposing queryid in
> the pg_stat_statements view?
>
> It would be highly useful when tracking changes over time. Right now I
> see people doing md5(query) to do that, which is a lot more ugly (and
> obviously uses more space and is slow, too).
Right. I continue to maintain that this is a good idea. I raised the
issue more than once. However, my proposal was not accepted by Tom and
Robert, apparently on the basis that queryId's actual value was
partially dictated by things like the endianness of the architecture
used, and the value of OIDs when serialising and subsequently hashing
the post-analysis tree.
What I'd like to be able to do is aggregate this information over time
and/or across standbys in a cluster, as queries are evicted and
subsequently re-entered into pg_stat_statement's shared hash table.
Now, there are situations were this isn't going to work, like when a
third-party logical replication system is used. That's unfortunate,
but I wouldn't expect it makes the information any less useful to the
large majority of people. I'd also credit our users with being
discerning enough to realise that they should not jump to the
conclusion that the value will be stable according to any particular
standard.
Arguments against including an internal value in the view could
equally well be applied to any of the internal statistic collector
views, many of which have oid columns, despite the fact that various
"name" columns already unambiguously identify tuples in most cases. I
see no reason for the inconsistency, particularly given that the
pg_stat_statements.query column *is* still somewhat ambiguous, as
described in the docs, and given that the query hash value referred to
in the docs anyway.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services