Re: Slow query on V12.

Em 23/09/2019 16:44, Tom Lane escreveu:
> =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes:
>> This is the query that is actually slow:
>> -- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
>> SELECT table_schema, table_name,
>>          n_live_tup::numeric as est_rows,
>>          pg_table_size(relid)::numeric as table_size
>>     FROM information_schema.columns
>>          JOIN pg_stat_user_tables as psut ON table_schema =
>> psut.schemanameAND table_name = psut.relname
>>          LEFT JOIN pg_statsON table_schema = pg_stats.schemanameAND
>> table_name = pg_stats.tablenameAND column_name = attname
>>    WHERE attname IS NULL
>>      AND table_schema NOT IN ('pg_catalog', 'information_schema')
>>    GROUP BY table_schema, table_name, relid, n_live_tup
> As a rule of thumb, mixing information_schema views and native
> PG catalog accesses in one query is a Bad Idea (TM).  There are
> a number of reasons for this, some of which have been alleviated
> as of v12, but it's still not going to be something you really
> want to do if you have an alternative.  I'd try replacing the
> use of information_schema.columns with something like
>
>    (pg_class c join pg_attribute a on c.oid = a.attrelid
>     and a.attnum > 0 and not a.attisdropped)
>
> (Hm, I guess you also need to join to pg_namespace to get the
> schema name.)  You could simplify the join condition with psut
> to be c.oid = psut.relid, though you're still stuck with doing
> schemaname+tablename comparison to join to pg_stats.
>
>             regards, tom lane

Thanks for the reply, but performance is still pretty bad:

Regular query: https://explain.depesz.com/s/CiPS
Tom's optimization: https://explain.depesz.com/s/kKE0

Sure,  37 seconds down to 8 seems pretty good, but on V11:

Regular query: https://explain.depesz.com/s/MMM9
Tom's optimization: https://explain.depesz.com/s/v2M8