Re: Slow query on V12.

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(at)siscobra(dot)com(dot)br <mailto:luisroberto(at)siscobra(dot)com(dot)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.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

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