Обсуждение: 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

Eric Gillum <eric@hazel.co> writes:
> Does not seem to improve if I turn off JIT.

AFAICT you're just getting sideswiped by the poor quality of our estimates
for messy sub-SELECTs :-(.  v12 is estimating that it will get only one
row out of the information_schema.table_constraints sub-select, which
causes it to decide that it'd be a cool idea to put that on the outside of
a nestloop with a moderately-expensive other arm.  But actually it gets
178 rows out, so the other arm gets iterated 178 times, and you lose.

         ->  Nested Loop  (cost=84.37..844.93 rows=1 width=232) (actual time=143.780..538.653 rows=9 loops=1)
               Join Filter: ((c_1.conname)::name = ("*SELECT* 1".constraint_name)::name)
               Rows Removed by Join Filter: 2127
               ->  Subquery Scan on "*SELECT* 1"  (cost=0.41..39.31 rows=1 width=128) (actual time=0.165..2.650
rows=178loops=1) 
...
               ->  Hash Join  (cost=83.96..805.59 rows=2 width=296) (actual time=0.448..3.007 rows=12 loops=178)

Now, v11 had the same poor estimate, but it happens not to make that
particular disastrous choice, in this particular case.  I think you
just got lucky on that before, though, and now are not lucky.
We did make some changes in v12 around the handling of conditions
involving information_schema.sql_identifier casts, which the info-schema
views are full of, so the idea that some other estimates might've
changed is not very surprising.  For the most part those changes
should've led to improved estimates, but in this case the big problem
is this unrelated bad estimate.

The crux of the misestimate seems to be here:

                                       ->  Seq Scan on pg_constraint c  (cost=0.00..20.98 rows=2 width=72) (actual
time=0.102..0.231rows=178 loops=1) 
                                             Filter: ((contype <> ALL ('{t,x}'::"char"[])) AND ((CASE contype WHEN
'c'::"char"THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN
'u'::"char"THEN 'UNIQUE'::text ELSE NULL::text END)::text = 'FOREIGN KEY'::text)) 

The "contype <> ALL ..." condition is actually estimated pretty well,
AFAICT, but the condition on a CASE clause is not something that the
planner has any understanding of, so it falls back to a default
estimate that is pretty far off for this case.

I noticed along the way that you show v12 as doing an unnecessary sort
that v11 was smart enough to avoid:

 Sort  (cost=1370.57..1370.59 rows=8 width=128) (actual time=10.416..10.417 rows=9 loops=1)
   Sort Key: "*SELECT* 1".table_name, ((a.attname)::information_schema.sql_identifier), (("*SELECT*
1_1".attname)::information_schema.sql_identifier)

 Sort  (cost=845.27..845.27 rows=1 width=256) (actual time=538.828..538.829 rows=9 loops=1)
   Sort Key: "*SELECT* 1".table_name, ((a.attname)::information_schema.sql_identifier), (("*SELECT*
1_1".relname)::information_schema.sql_identifier),(("*SELECT* 1_1".attname)::information_schema.sql_identifier) 

It's not actually necessary to sort on the relname (third sort key)
because your WHERE clause constrains that to be a constant.  v11 saw
that but v12 fails to, which is a bug that I just pushed a fix for.
It's not terribly important for your example, but it might matter more
in other cases.

As far as solving your larger problem goes, I don't have any near-term
ideas other than to rewrite the query to look directly at the system
catalogs rather than going through information_schema views.  But that's
not a very nice answer.

            regards, tom lane