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
