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,
<luisrobe...@siscobra.com.br <mailto:luisrobe...@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.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
