pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding the size of your biggest relations
4+ messages / 3 participants
[nested] [flat]
* Finding the size of your biggest relations
@ 2016-09-23 12:57 Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 4+ messages in thread
From: Artem Tomyuk @ 2016-09-23 12:57 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Finding the size of your biggest relations
@ 2016-09-23 13:21 hubert depesz lubaczewski <depesz@depesz.com>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 1 reply; 4+ messages in thread
From: hubert depesz lubaczewski @ 2016-09-23 13:21 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Finding the size of your biggest relations
@ 2016-09-23 13:31 Artem Tomyuk <admin@leboutique.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 4+ messages in thread
From: Artem Tomyuk @ 2016-09-23 13:31 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Finding the size of your biggest relations
@ 2016-09-23 14:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Artem Tomyuk <admin@leboutique.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2016-09-23 14:30 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; +Cc: depesz@depesz.com; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-09-23 14:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-23 12:57 Finding the size of your biggest relations Artem Tomyuk <admin@leboutique.com>
2016-09-23 13:21 ` hubert depesz lubaczewski <depesz@depesz.com>
2016-09-23 13:31 ` Artem Tomyuk <admin@leboutique.com>
2016-09-23 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox