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.
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
