Re: SearchSysCache() tutorial?

On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey <pramsey(at)cleverelephant(dot)ca>
wrote:

> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey <pramsey(at)cleverelephant(dot)ca>
> wrote:
> > Is there anywhere any documentation on SearchSysCache? I find I end up
> > on these long spelunking expeditions through the code base for a
> > particular problem, find the answer after many hours, then forget
> > everything I learned because I don't exercise my knowledge frequently
> > enough. A decent reference guide would help a lot. What do the various
> > SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> > etc.
> >
> > I can accept if there are not, but it would be a shame to keep on
> > hunting like this if there were a good reference lying around.
>
> My particular hunt today is "for a given table relation, find any
> indexes that use the gist access method and are on a single attribute
> of type geometry".
>
>
>My particular hunt today is "

*for a given table relation, find any >indexes that use the gist access
method and are on a single attribute >of type geometry".*

For that information, you are better off querying the system catalogs!

*https://www.postgresql.org/docs/9.6/static/catalogs.html
<https://www.postgresql.org/docs/9.6/static/catalogs.html>*

adjust the WHERE clause below to include the attribute you are looking for.

SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
idx.indisexclusion,
pg_get_indexdef(idx.indexrelid),
CASE WHEN idx.indisvalid
THEN 'valid'
ELSE 'INVALID'
END as statusi,
pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname)) as size_in_bytes,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname))) as size
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
WHERE i.relname = 'your_table'
AND n.nspname NOT LIKE 'pg_%'
AND pg_get_indexdef(idx.indexrelid) LIKE '%gist%'
ORDER BY 1, 2, 3;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.