Re: SearchSysCache() tutorial?

On Dec 11, 2017, at 11:37 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:

On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey <pramsey@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

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;