Обсуждение: Slow query on V12.

Hi!

Recently I've been looking for bloat in my databases and found a query to show which tables are more bloated and by how much.

This is the explain plan on v12.3: https://explain.depesz.com/s/8dW8C
And this is with v11: https://explain.depesz.com/s/diXY

Both databases have approx. the same size and have the same schema, but on v12 I the query takes much longer to run.

Em 23/09/2019 15:43, nikhil raj escreveu:

Hi,

Can you check by vacuum analyze  the database. And run the query.

**Remember don't  use Vacuum full.

On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, <luisroberto@siscobra.com.br> wrote:

Hi!

Recently I've been looking for bloat in my databases and found a query to show which tables are more bloated and by how much.

This is the explain plan on v12.3: https://explain.depesz.com/s/8dW8C
And this is with v11: https://explain.depesz.com/s/diXY

Both databases have approx. the same size and have the same schema, but on v12 I the query takes much longer to run.

Hi!

Thanks for the reply!

Here's the plan after running vacuum analyze: https://explain.depesz.com/s/lhcl

There was no difference in execution time.

Em 23/09/2019 16:03, Luís Roberto Weck escreveu:

Em 23/09/2019 15:43, nikhil raj escreveu:

Hi,

Can you check by vacuum analyze  the database. And run the query.

**Remember don't  use Vacuum full.

On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, <luisroberto@siscobra.com.br> wrote:

Hi!

Recently I've been looking for bloat in my databases and found a query to show which tables are more bloated and by how much.

This is the explain plan on v12.3: https://explain.depesz.com/s/8dW8C
And this is with v11: https://explain.depesz.com/s/diXY

Both databases have approx. the same size and have the same schema, but on v12 I the query takes much longer to run.

Hi!

Thanks for the reply!

Here's the plan after running vacuum analyze: https://explain.depesz.com/s/lhcl

There was no difference in execution time.

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.schemaname AND table_name = psut.relname
       LEFT JOIN pg_stats ON table_schema = pg_stats.schemaname AND table_name = pg_stats.tablename AND 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

If I turn the left join to a inner join, the query runs very fast.

Plans:
 LEFT JOIN: https://explain.depesz.com/s/i88x
 INNER JOIN: https://explain.depesz.com/s/ciSu

Ofcourse, that's not what the full query needs

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

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