What is the correct way to find 'missing indexes'in postgresql

Nikhy

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
@Nikhil I say that's a strong indicator. If you have a table with 10.000.000 index scans and 100.000 seqscans, those seqscans can still break your neck easily.

So there's no way to define a general rule of thumb
Nikhy5y ago
so you say that if I have 1 more seqscan than index scan you would consider making new/update index? I am really asking to understand
Rajanikant Hawaldar5y ago
@Nikhil guess so
Nikhy5y ago
@rajanikant this "1" means that there was 1 more seqscan than index scan no?
Rajanikant Hawaldar5y ago
@Nikhil Well, 1 mio seqscans would be pretty bad, no?
Nikhy5y ago
@rajanikant but then it can be that seqscan=1,000,001 and index_scan=1,000,000 :/ i think I need to do seqscan - index_scan and get the biggest , no? I am so bad in writing those queries
Rajanikant Hawaldar5y ago
@Nikhil That's a pretty strong indicator, yes.
Nikhy5y ago
@Rajanikant so once seqscan > index scan you would consider it as "missing index"?
Rajanikant Hawaldar5y ago
Look for tables with high seqscan counts. They shouldn't be too small though (your "core_data" table with just one page won't use an index usually).
Salman Beg5y ago
Hey check this link which explains the same thing what you are looking for, https://salayhin.wordpress.com/2018/01/02/finding-missing-index-in-postgresql/ https://www.dbrnd.com/2015/10/postgresql-script-to-find-a-missing-indexes-of-the-schema/ https://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis