What does `select 100 * (sum(idx_blks_

What does select 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as index_hit_rate FROM pg_statio_user_indexes; show?

16 Replies

vicious-gold•16mo ago

@ShinyPokemon ERROR: division by zero (SQLSTATE 22012)

sunny-green•16mo ago

Try just select idx_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_indexes;

vicious-gold•16mo ago

gotcha, here you go

sparkling-field-0437...

vicious-gold•16mo ago

total usage metrics

sunny-green•16mo ago

Do you have any indexes defined? This suggests not, or that your queries aren't using them

vicious-gold•16mo ago

we definitely do. I did a quick EXPLAIN on one of our queries and see indices

vicious-gold•16mo ago

Here's another example of a frequent query

sunny-green•16mo ago

I think you accidentally leaked your username and password in that first screenshot. I suggest rotating it Will take a look at the explain in a moment It might be that your dataset it too large to fit in cache? I tested with a small dataset:

SELECT
    indexrelname AS index_name,
    idx_blks_read,
    idx_blks_hit,
    (idx_blks_hit * 100.0 / NULLIF(idx_blks_read + idx_blks_hit, 0)) AS index_cache_hit_ratio
FROM
    pg_statio_user_indexes
WHERE
    indexrelname = 'replace_me';

SELECT
    indexrelname AS index_name,
    idx_blks_read,
    idx_blks_hit,
    (idx_blks_hit * 100.0 / NULLIF(idx_blks_read + idx_blks_hit, 0)) AS index_cache_hit_ratio
FROM
    pg_statio_user_indexes
WHERE
    indexrelname = 'replace_me';

I get the following:

#index_name    idx_blks_read    idx_blks_hit    index_cache_hit_ratio
created_at_idx    1    86408    99.9988427131433068

#index_name    idx_blks_read    idx_blks_hit    index_cache_hit_ratio
created_at_idx    1    86408    99.9988427131433068

Sure looks like your index is there, queries are just not served from cache

vicious-gold•16mo ago

is there any guidance on how "big" my data should be to fit in the cache? We have 139 tables and run multiple queries, i'm surpised 0% of them are working out

sunny-green•16mo ago

139 tables with indexes and data sounds like a lot on the free tier (I'm guessing you're on free based on the screenshot) let me see can I get someone to look into it

vicious-gold•16mo ago

thank you so much @ShinyPokemon , i'd really appreciate it @ShinyPokemon we are actaully on a paid plan lol, the UI may be because I'm an invitee to a paid project

sunny-green•16mo ago

Aaaaaah ok. Have you tried a larger CU size?

vicious-gold•16mo ago

this is what it's currently set to. I'd assume it'll scale to 3 if needed

sunny-green•16mo ago

Since you’re on a paid plan for this project, I’d recommend opening a support ticket

vicious-gold•16mo ago

thanks @ShinyPokemon , makes sense! I just opened a support ticket

sunny-green•16mo ago

Definitely. Someone will take look under the covers with you 🙂

Gaming

Programming

What does `select 100 * (sum(idx_blks_

Did you find this page helpful?