Thread: pg_stat_statement normalization fails due to temporary tables
Hi, pg_stat_statement's query normalization fails when temporary tables are used in a query. That's because JumbleRangeTable() uses the relid in the RTE to build the query fingerprint. I think in this case pgss from 9.1 actually would do better than 9.2+ as the hash lookup previously didn't use the relid. I don't really have a good idea about fixing this though. The best thing that comes to mind is simply use eref->aliasname for the disambiguation... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> pg_stat_statement's query normalization fails when temporary tables are
> used in a query. That's because JumbleRangeTable() uses the relid in the
> RTE to build the query fingerprint. I think in this case pgss from 9.1
> actually would do better than 9.2+ as the hash lookup previously didn't
> use the relid.
> I don't really have a good idea about fixing this though. The best thing
> that comes to mind is simply use eref->aliasname for the
> disambiguation...
Hmm ... by "fails" I suppose you mean "doesn't treat two different
instances of the same temp table name as the same"? I'm not sure
that's a bug.
If we go over to using the aliasname then "select x from foo a" and
"select x from bar a" would be treated as the same query, which
clearly *is* a bug. More generally, I don't think that schema1.tab
and schema2.tab should be considered the same table for this purpose.
So it's hard to see how to do much better than using the OID.
regards, tom lane
On 2014-12-02 09:59:00 -0500, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > pg_stat_statement's query normalization fails when temporary tables are > > used in a query. That's because JumbleRangeTable() uses the relid in the > > RTE to build the query fingerprint. I think in this case pgss from 9.1 > > actually would do better than 9.2+ as the hash lookup previously didn't > > use the relid. > > > I don't really have a good idea about fixing this though. The best thing > > that comes to mind is simply use eref->aliasname for the > > disambiguation... > > Hmm ... by "fails" I suppose you mean "doesn't treat two different > instances of the same temp table name as the same"? I'm not sure > that's a bug. Well, it did work < 9.2... > If we go over to using the aliasname then "select x from foo a" and > "select x from bar a" would be treated as the same query, which > clearly *is* a bug. Yep. The fully qualified name + alias would probably work - but IIRC isn't available in the RTE without further syscache lookups... > More generally, I don't think that schema1.tab > and schema2.tab should be considered the same table for this purpose. > So it's hard to see how to do much better than using the OID. Well, from a practical point of view it's highly annoying if half of pg_stat_statement suddenly consists out of the same query, just issued in a different session. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services