PostgreSQL Administration

Author

Question

Mark Steben

2006-11-07, 7:18 pm

I am very new to PostgreSQL. Is it appropriate to pose questions to this
email list?

I am trying to come up with a query that will list the names of the database
indexes that

Have been chosen as clustering indexes. I know I can get the INDEXRELID
from PG.INDEX

But have yet to figure out how to get the index name from there. Any help
would be appreciated.

And, if this is not an appropriate forum to ask questions please tell me.

Thank you,

Mark Steben

AutoRevenue

Richard Broersma Jr

2006-11-07, 7:18 pm

> I am very new to PostgreSQL. Is it appropriate to pose questions to this
> email list?
> I am trying to come up with a query that will list the names of the database
> indexes that
>
> Have been chosen as clustering indexes. I know I can get the INDEXRELID
> from PG.INDEX
>
> But have yet to figure out how to get the index name from there. Any help
> would be appreciated.
>
> And, if this is not an appropriate forum to ask questions please tell me.

I assume this is an appropiate list for this question. I am sure that pg_general would have
worked also. With-out knowing the postgresql pg_* schema very well I can be difficult to
determine queries that will give your this sort of information. In my case I like to cheat.

1) I turn on statement logging.
2) I issue a commands like \d from psql interface
3) Then, I look at the pg_logs to see what sql statement was actually created from "\d"

In your case I would first determine which psql function will give you the information you want
and this use it to give you the query you need.

I hope this helps.

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jeff Frost

2006-11-07, 7:18 pm

On Tue, 7 Nov 2006, Mark Steben wrote:

> I am very new to PostgreSQL. Is it appropriate to pose questions to this
> email list?

It might be better on pgsql-sql, but I'm not sure.

> I am trying to come up with a query that will list the names of the database
> indexes that
>
> Have been chosen as clustering indexes. I know I can get the INDEXRELID
> from PG.INDEX
>
> But have yet to figure out how to get the index name from there. Any help
> would be appreciated.
>
> And, if this is not an appropriate forum to ask questions please tell me.

You want to join on pg_class.oid. You can see the info on pg_index here in
the docs: http://www.postgresql.org/docs/8.1/...g-pg-index.html

select i.indexrelid,c.relname from pg_index i, pg_class c where i.indexrelid =
c.oid;

will likely give you what you're after.

--
Jeff Frost, Owner < jeff@frostconsulting
llc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Scott Marlowe

2006-11-07, 7:18 pm

On Tue, 2006-11-07 at 14:27, Richard Broersma Jr wrote:
>
> I assume this is an appropiate list for this question. I am sure that pg_general would have
> worked also. With-out knowing the postgresql pg_* schema very well I can be difficult to
> determine queries that will give your this sort of information. In my case I like to cheat.
>
> 1) I turn on statement logging.
> 2) I issue a commands like \d from psql interface
> 3) Then, I look at the pg_logs to see what sql statement was actually created from "\d"

Easier way to cheat:

psql -E dbname

then all the \d commands and what not will spill their secrets right on
the screen. :)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Richard Broersma Jr

2006-11-07, 7:18 pm

---> Easier way to cheat:
>
> psql -E dbname
>
> then all the \d commands and what not will spill their secrets right on
> the screen. :)

Cool, thanks for the tip.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Mark Steben

2006-11-07, 7:18 pm

Thank you, Jeff. That worked like a champ. My initial problem
Had to do with not understanding the concept of OID datatypes.
My next learning task is to read up on OID.

Thanks again, Mark

-----Original Message-----
From: jeff@frostconsulting
llc.com & #91;mailto:jeff@fros
tconsultingllc.com]
Sent: Tuesday, November 07, 2006 3:29 PM
To: Mark Steben
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question

On Tue, 7 Nov 2006, Mark Steben wrote:

> I am very new to PostgreSQL. Is it appropriate to pose questions to this
> email list?

It might be better on pgsql-sql, but I'm not sure.

> I am trying to come up with a query that will list the names of the
database
> indexes that
>
> Have been chosen as clustering indexes. I know I can get the INDEXRELID
> from PG.INDEX
>
> But have yet to figure out how to get the index name from there. Any help
> would be appreciated.
>
> And, if this is not an appropriate forum to ask questions please tell me.

You want to join on pg_class.oid. You can see the info on pg_index here in
the docs: http://www.postgresql.org/docs/8.1/...g-pg-index.html

select i.indexrelid,c.relname from pg_index i, pg_class c where i.indexrelid
=
c.oid;

will likely give you what you're after.

--
Jeff Frost, Owner < jeff@frostconsulting
llc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

adey

2006-11-07, 7:18 pm

I think the following query should list clustered indexes for you:-

select * from pg_index
where indisclustered = 't'

On 11/8/06, Mark Steben <msteben@autorevenue.com> wrote:
>
> Thank you, Jeff. That worked like a champ. My initial problem
> Had to do with not understanding the concept of OID datatypes.
> My next learning task is to read up on OID.
>
> Thanks again, Mark
>
> -----Original Message-----
> From: jeff@frostconsulting
llc.com & #91;mailto:jeff@fros
tconsultingllc.com]
> Sent: Tuesday, November 07, 2006 3:29 PM
> To: Mark Steben
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Question
>
> On Tue, 7 Nov 2006, Mark Steben wrote:
>
> this
>
> It might be better on pgsql-sql, but I'm not sure.
>
> database
> help
> me.
>
> You want to join on pg_class.oid. You can see the info on pg_index here
> in
> the docs: http://www.postgresql.org/docs/8.1/...g-pg-index.html
>
> select i.indexrelid,c.relname from pg_index i, pg_class c where
> i.indexrelid
> =
> c.oid;
>
> will likely give you what you're after.
>
> --
> Jeff Frost, Owner < jeff@frostconsulting
llc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Sponsored Links