> On Dec 11, 2017, at 11:37 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > > On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey <pram...@cleverelephant.ca > <mailto:pram...@cleverelephant.ca>> wrote: > On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey <pram...@cleverelephant.ca > <mailto:pram...@cleverelephant.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
