Re: information_schema performance in Postgres 12

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>