Обсуждение: Finding the size of your biggest relations
Hi all.
i am running this query to get to 20 biggest tables but getting only pg_temp** records like this without size:
"pg_toast_temp_39.pg_toast_1384618609";""
"pg_toast_temp_146.pg_toast_1384509547_index";""
"pg_temp_5.tt_photo";""
"pg_toast_temp_146.pg_toast_1384494807";""
"pg_temp_58.tt_category_action_brands_АкцияГУИД_idx";""
"pg_toast_temp_56.pg_toast_1384618787_index";""
"pg_toast_temp_47.pg_toast_1384618711";""
"pg_toast_temp_58.pg_toast_1384618621_index";""
"pg_toast_temp_5.pg_toast_1384618426_index";""
"pg_temp_32.tt1";""
"pg_temp_5.tt_accessory_kit_composition_НоменклатураГУИД_idx";""
"pg_toast_temp_112.pg_toast_1384472524";""
"pg_toast_temp_27.pg_toast_1384618423_index";""
How can i fix it? As i understand this is temp tables that was created during run of some heavy queries that didn't fit in to work_mem and was dumped on disk.
How i can remove it from stats, just restart postgre will help?
pgsql_tmp dir doesn't contain those files, so i can assume that they already deleted from disk?
Thanks!
On Fri, Sep 23, 2016 at 03:57:52PM +0300, Artem Tomyuk wrote: > i am running this query to get to 20 biggest tables but getting only > pg_temp** records like this without size: What query did you run? For finding largest tables, I use: select oid::regclass, pg_table_size(oid) from pg_class where relkind = 'r' order by 2 desc limit 20; Best regards, depesz
Sorry,
this query
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;
2016-09-23 16:21 GMT+03:00 hubert depesz lubaczewski <depesz@depesz.com>:
On Fri, Sep 23, 2016 at 03:57:52PM +0300, Artem Tomyuk wrote:
> i am running this query to get to 20 biggest tables but getting only
> pg_temp** records like this without size:
What query did you run?
For finding largest tables, I use:
select oid::regclass, pg_table_size(oid) from pg_class where relkind
= 'r' order by 2 desc limit 20;
Best regards,
depesz
Artem Tomyuk <admin@leboutique.com> writes:
> Sorry,
> this query
> SELECT nspname || '.' || relname AS "relation",
> pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> AND C.relkind <> 'i'
> AND nspname !~ '^pg_toast'
> ORDER BY pg_total_relation_size(C.oid) DESC
> LIMIT 20;
I think what's happening is that your ORDER BY is sorting nulls first.
So what's coming out at the top is temp relations that were dropped
between the time of the statement snapshot and the time that
pg_total_relation_size looked at them. Try ORDER BY ... DESC NULLS LAST.
regards, tom lane