I had not run the script per se, I had run ANALYZE. I just re-upgraded to a separate cluster and ran the provided script. The query still takes about 500ms.
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.
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 >