Re: information_schema performance in Postgres 12

On 2/25/20 8:53 PM, Eric Gillum wrote:
> Hello,
>
> I've noticed a ~50x regression in execution time for a query when moving
> from Postgres 11.6 to 12.1. Here's an example:
>
> SELECT tc.table_name, kcu.column_name, ccu.table_name AS
> foreign_table_name, ccu.column_name AS foreign_column_name FROM
> information_schema.table_constraints tc JOIN
> information_schema.key_column_usage kcu ON tc.constraint_name =
> kcu.constraint_name JOIN information_schema.constraint_column_usage ccu
> ON ccu.constraint_name = tc.constraint_name WHERE tc.constraint_type =
> 'FOREIGN KEY' AND ccu.table_name = 'patient' ORDER BY tc.table_name,
> kcu.column_name, ccu.table_name, ccu.column_name;
>
> The only parameter to the query is the table name, in this case
> 'patient'. My schema has maybe 50 tables and no table has more than 50
> columns. Most tables have around one to three foreign keys.
>
> I did as straightforward a pg_upgrade as I could, so I don't know what
> the difference there would be.

Did you do?:
https://www.postgresql.org/docs/12/pgupgrade.html

14. Statistics

Because optimizer statistics are not transferred by pg_upgrade, you will
be instructed to run a command to regenerate that information at the end
of the upgrade. You might need to set connection parameters to match
your new cluster.

>
> Insight much appreciated. My thought is this is a large difference in
> execution time, and I'd like to know if I can get that time back.
> Anyway, I could move toward caching the results of these queries, so
> it's not the worst thing that could've happened. Overall 12.1 is looking
> like a godsend over 11.6 for many other use cases I have!
>
> PostgreSQL 11.6 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
> version 8.1.0 (clang-802.0.42), 64-bit
> PostgreSQL 12.1 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
> version 8.1.0 (clang-802.0.42), 64-bit
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com