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
