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 Wed, Feb 26, 2020 at 7:56 AM Adrian Klaver <adrian.kla...@aklaver.com> wrote: > 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.kla...@aklaver.com >
