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.klaver@aklaver.com