Em 23/09/2019 16:44, Tom Lane escreveu:
=?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@xxxxxxxxxxxxxxx> 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_tupAs 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
