How can I detect genie and essence
how can I detect GIN and GiST indexes in postgresql? I'm looking if postgres database uses full text. I think the table uses GIN o GiST and then uses the full text.
I accept that GIN or GiST indexes do not necessarily mean that they are used for full text search, but how can I distinguish them from other types of indexes? I want to list Gin Gist indices
source to share
Index definitions can be recreated using pg_get_indexdef()
from the System Catalog Information Functions supplied by the System View pg_index
. The output is theoretically non-trivial to parse, but in practice a simple regex is good enough to filter these definitions for certain types of indexes.
For example, to find the indices gist
or gin
, you can use a query like this:
SELECT pg_get_indexdef(indexrelid) from pg_index
WHERE pg_get_indexdef(indexrelid) ~ 'USING (gin |gist )';
To find if a specific database is using full text, I would most likely search for columns tsvector
, with a query like this:
SELECT table_schema,table_name, column_name
FROM information_schema.columns
WHERE data_type='tsvector';
source to share
I dug into the Postgres source code and found this information is stored in a table pg_am
:
select i.relname, a.amname
from pg_index ix
left join pg_class i on ix.indexrelid = i.oid
left join pg_class t on ix.indrelid = t.oid
left join pg_am a on i.relam = a.oid
source to share