How to find the link between a table and her indexes??

From: "Patrick De Zlio" <pchemla01(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: How to find the link between a table and her indexes??
Date: 2007-10-23 13:30:47
Message-ID: 58729a240710230630t1d7c9d83r59531526a31ed72@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I'm trying to find all indexes linked to a table.

looking in pg_class with the name of my table in my scheme 16408:

select * from pg_class where relnamespace = 16408 and relname = 'shop'

I get :
"shop";16408;16543;16390;0;16542;16393;356244;175852;16554;0;t;f;"r";43;0;5;0;0;0;t;t;f;t;1470535582;"{xxxxxx=xxxxxx/xxxxxx,xxxxxx=r/xxxxxxx}";""

The relfilenode is 16542. Looking this relfilenode in the pg_index table as
indrelid:

select * from pg_index where indrelid = 16542

I get 17 rows (I have 15 indexes on this table in my scheme+2 in another
scheme):
52350439;16542;2;t;f;f;t;"4 25";"2003 1978";"";""
52350553;16542;1;t;t;f;t;"1";"1980";"";""
52350753;16542;1;f;f;f;t;"2";"1980";"";""
52350828;16542;1;f;f;f;t;"22";"2003";"";""
52350829;16542;1;f;f;f;t;"26";"2003";"";""
52350834;16542;1;f;f;f;t;"10";"1978";"";""
52350835;16542;1;f;f;f;t;"9";"1988";"";""
52350836;16542;1;f;f;f;t;"12";"2003";"";""
52350837;16542;1;f;f;f;t;"8";"1988";"";""
52350838;16542;1;f;f;f;t;"35";"2003";"";""
52350848;16542;1;f;f;f;t;"24";"1976";"";""
52350849;16542;1;f;f;f;t;"25";"1978";"";""
52350850;16542;1;f;f;f;t;"11";"1976";"";""
52350851;16542;1;f;f;f;t;"3";"2003";"";""
52350923;16542;1;f;f;f;t;"1";"1980";"";""
52350928;16542;1;f;f;f;t;"2";"1980";"";""
107685548;16542;1;f;f;f;t;"30";"2003";"";""

But if I try to search back in pg_class for relfilenode in those values I
get no answer.

This index is one of the 15 indexes attached to this table. There is nothing
clearly linked to my table.

"idx_shop_title";16408;0;16390;403;159375570;16402;2064;175852;0;0;f;f;"i";1;0;0;0;0;0;f;f;f;f;0;"";""

If I do the same on a copy of the production database, every thing is fine,
but the copy is adaily clean export/import built each night.

Someone could help me retrieve each indexes attached to each table??

Thanks for help.
Patrick

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Martin Strejc 2007-10-23 14:07:43 pg_toast
Previous Message José Roberto Motta Garcia 2007-10-23 13:04:18 postmaster.pid