Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Patrick De Zlio"
Date:


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

 

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

From
Tom Lane
Date:
"Patrick De Zlio" <pchemla01@gmail.com> writes:
> The relfilenode is 16542. Looking this relfilenode in the pg_index table as
> indrelid:
> select * from pg_index where indrelid = 16542

The key is OID, not relfilenode.  See the system catalog documentation.

            regards, tom lane