current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Whenever you need to examine the structure of your database via code, always think "I should look at information_schema or pg_catalog". information_schema contains a standardized schema (66 views), whereas pg_catalog is PostgreSQL-specific, but contains more info (97 tables or views).

select 
  * 
from
  pg_catalog.pg_indexes 
where 
  indexdef ~* '\ygist\y'

Will show you all the gist indexes and their tables, names, and definitions.

~* means match a regular expression, case-insensitive.

\y means word boundary, so it would find ' gist ' but not 'logistics' in that column

share|improve this answer
    
Thanks! This worked! The command did not produce any results, but that was somewhat expected as most of our indexes are btree. –  user972276 Aug 8 '14 at 16:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.