On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey <pram...@cleverelephant.ca> wrote:
> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey <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; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
