From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Paul Ramsey <pramsey(at)cleverelephant(dot)ca> |
Cc: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SearchSysCache() tutorial? |
Date: | 2017-12-11 19:37:37 |
Message-ID: | CANu8Fix6J+FE8JUFGnJktuj5QzkBnk35JT1Dg3NarLVeigkQQA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Ramsey | 2017-12-11 19:39:40 | Re: SearchSysCache() tutorial? |
Previous Message | Paul Ramsey | 2017-12-11 19:26:51 | Re: SearchSysCache() tutorial? |