Determine if an index is a B-tree, GIST, or something else?

On Jan 17, 2013, at 10:03 AM, Paul Jungwirth wrote:

Is there any way to determine, by querying pg_index and other pg_* tables, whether an index was created as `USING something`? I've already got a big query joining pg_class, pg_index, etc. to pull out various attributes about the indexes in my database, and I'd like to include whether it's a GIST index, a B-Tree, or whatever was in the USING clause when the index was created.

I see that I can join with the pg_opclass table, but that has 100+ rows, and I'm not sure how to tell what is a B-Tree and what isn't. Any advice?

pg_indexes (not pg_index) seems to have the data you're looking for, unless I misunderstood the question.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

On Thu, 2013-01-17 at 10:03 -0800, Paul Jungwirth wrote:

Is there any way to determine, by querying pg_index and other pg_* tables,
whether an index was created as `USING something`? I've already got a big
query joining pg_class, pg_index, etc. to pull out various attributes about
the indexes in my database, and I'd like to include whether it's a GIST
index, a B-Tree, or whatever was in the USING clause when the index was
created.

You can either look at pg_indexes, or use pg_get_indexdef(oid) function,
where oid is index's oid.

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

pg_indexes (not pg_index) seems to have the data you're looking for,

unless I misunderstood the question.

That is a lovely table, but I want to get each attribute individually,
without having to parse the CREATE INDEX .... statement.

It looks like I was almost there with pg_opclass. This will tell me what
kind of index is required for each operator:

select opcnamespace, opcname, amname from pg_opclass o, pg_am a where
o.opcmethod = a.oid;

So in principle I can just join pg_index, pg_opclass, and pg_am to get my
answer. It's actually a little more complicated because pg_index.indclass
is not an oid, but an oidvector, with one entry for each column in the
index. But unless I'm mistaken, every column in given index must use the
same index method. For instance in a 2-column index you can't say `USING
(btree, gist)`. So I can join with `pg_index.indclass[0] = pg_opclass.oid`.
Can anyone confirm for me that for any index, every pg_opclass it uses will
have the same pg_am?

Thanks,
Paul

Paul Jungwirth <pj@illuminatedcomputing.com> writes:

So in principle I can just join pg_index, pg_opclass, and pg_am to get my
answer. It's actually a little more complicated because pg_index.indclass
is not an oid, but an oidvector, with one entry for each column in the
index. But unless I'm mistaken, every column in given index must use the
same index method. For instance in a 2-column index you can't say `USING
(btree, gist)`. So I can join with `pg_index.indclass[0] = pg_opclass.oid`.
Can anyone confirm for me that for any index, every pg_opclass it uses will
have the same pg_am?

pg_opclass seems the hard way --- just use pg_class.relam, which is the
OID of the index's AM. (And yes, all the opclasses had better match
that.)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pg_opclass seems the hard way --- just use pg_class.relam, which is
the OID of the index's AM.

Ah, that works like a charm. Thanks!

Paul