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

Question

Rajanikant Hawaldar · Answer

@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

Nikhy · Answer

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 Hawaldar · Answer

@Nikhil guess so

Nikhy · Answer

@rajanikant this "1" means that there was 1 more seqscan than index scan no?

Rajanikant Hawaldar · Answer

@Nikhil Well, 1 mio seqscans would be pretty bad, no?

Nikhy · Answer

@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 Hawaldar · Answer

@Nikhil That's a pretty strong indicator, yes.

Nikhy · Answer

@Rajanikant so once seqscan > index scan you would consider it as "missing index"?

Rajanikant Hawaldar · Answer

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 Beg · Answer

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

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

Answers (10)