Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E443E9FBF43 for ; Tue, 23 Oct 2007 10:30:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 22212-07 for ; Tue, 23 Oct 2007 10:30:44 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 1024F9FB60F for ; Tue, 23 Oct 2007 10:30:51 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so1031312nfh for ; Tue, 23 Oct 2007 06:30:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=hwotrMtLblaTziOSiKQutODi2W4AHYpjBKauoFf3PRY=; b=SUmFk/FUH5yjDc6ZBWm15PbaUTzpDtmalXaRX0idvM65w5mZsgHTMulKybGSRCq5U807g3Jp+05gXiMC+4U1xHu4lWJLbZIg5WPZTkIhxqHIqZyYy/bW68D6cvOYbRIkj49R7hNhEhVVOGyvYR1kBCQ+piKqEgr1M1C8sTf+Bqc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=HelJkRjfL3+0vuLAs0C4yp7AB8e5cdjBDbCk9qpnUVf2vxjB4sYKcHTvt3A54FRQ7OAEqR984wl7FxYNxNnJJhBy8v3VFvcMBMj0TMOi71MT/e6yISN3zmyftgFjJ2WEhYCJMlV33a3kTGGJYipqjNyyYz1DiKPRmJEmDBSEeRw= Received: by 10.82.175.17 with SMTP id x17mr1757525bue.1193146247553; Tue, 23 Oct 2007 06:30:47 -0700 (PDT) Received: by 10.82.122.16 with HTTP; Tue, 23 Oct 2007 06:30:47 -0700 (PDT) Message-ID: <58729a240710230630t1d7c9d83r59531526a31ed72@mail.gmail.com> Date: Tue, 23 Oct 2007 15:30:47 +0200 From: "Patrick De Zlio" To: pgsql-admin@postgresql.org Subject: How to find the link between a table and her indexes?? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_633_15119680.1193146247546" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.657 tagged_above=0 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001, WEIRD_QUOTING=1.396 X-Spam-Level: X-Archive-Number: 200710/215 X-Sequence-Number: 27129 ------=_Part_633_15119680.1193146247546 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_633_15119680.1193146247546 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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

 
------=_Part_633_15119680.1193146247546--