Re: SearchSysCache() tutorial?

> On Dec 11, 2017, at 11:37 AM, Melvin Davidson <melvin6925(at)gmail(dot)com> wrote:
>
>
>
> On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey <pramsey(at)cleverelephant(dot)ca <mailto:pramsey(at)cleverelephant(dot)ca>> wrote:
> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey <pramsey(at)cleverelephant(dot)ca <mailto: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;

Thanks. I’m working on doing this at the C level however, so using syscache seems like the right way to go about it. I’d like to avoid doing an SPI thing, if I can, tho I suppose I could always suck it up and just do that.

P