From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow query on V12.
In-reply-to: <d40a8e6b-676d-7584-f646-d78d625e104d@siscobra.com.br>
References: <eaf7b5fa-d832-ca49-2811-2d3ab21a5444@siscobra.com.br>
 <CAG1ps1zzAeB9Z3PEhQt5zVLjGocgvWWZMkhVq-NhPF7VKrn+3g@mail.gmail.com>
 <13d5836f-27c0-d8c3-0106-6ba0160fcd2b@siscobra.com.br>
 <d40a8e6b-676d-7584-f646-d78d625e104d@siscobra.com.br>
Comments: In-reply-to =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?=
 <luisroberto@siscobra.com.br>
	message dated "Mon, 23 Sep 2019 16:12:02 -0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <17615.1569267863.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 23 Sep 2019 15:44:23 -0400
Message-ID: <17616.1569267863@sss.pgh.pa.us>
Precedence: bulk

=?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