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@xxxxxxxxxxx
