19

I have an autovacuum process running on pg_toast:

select query, from pg_stat_activity where query like '%autov%';
"autovacuum: VACUUM pg_toast.pg_toast_15404513 "

How do I find out what table/index/whatever this pg_toast pertains to? Or is the autovacuum working on something else?

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Henley
  • 21,258
  • 32
  • 119
  • 207

2 Answers2

40

Here's a shorter way:

select 15404513::regclass;

where 15404513 is pg_toast_ suffix.

Tombart
  • 30,520
  • 16
  • 123
  • 136
GreenReaper
  • 1,121
  • 1
  • 15
  • 23
17

I think you'll want something like:

select n.nspname, c.relname 
from pg_class c 
inner join pg_namespace n on c.relnamespace = n.oid
where reltoastrelid = (
    select oid
    from pg_class 
    where relname = 'pg_toast_15404513' 
    and relnamespace = (SELECT n2.oid FROM pg_namespace n2 WHERE n2.nspname = 'pg_toast') )

It'd be nice if Pg reported this in the vacuum command summary.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Is there a way to list real name of all pg_toast_xxx ? thanks. – wolf97084 Aug 11 '15 at 15:51
  • 2
    @wolf97084, here is query for listing all pg_toast_xxx and their real name `select r.relname, t.relname from pg_class r inner join pg_class t on t.reltoastrelid = r.oid;` – SergA Feb 21 '17 at 15:37