Thread: SearchSysCache() tutorial?
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!
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;
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
There is no guarantee that information will be in syscache at any point in time. It will, however, always be in the postgreSQL catalogs. That is the whole point
of having them, and the SQL language.On Mon, Dec 11, 2017 at 2:39 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:
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.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!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;P
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.