search

mfvanek / pg-index-health

pg-index-health is a Java library for analyzing and maintaining indexes and tables health in Postgresql databases.
Apache License 2.0
124 stars 14 forks source link

Idea: how to catch errors of the "missing index" class #360

Open mfvanek opened 5 months ago

mfvanek commented 5 months ago

In integration tests, at the very beginning, we set enable_seqscan to off. What will force the scheduler to avoid seq scan, if it has such an opportunity (there is an index)

At the end of the test, we look at the statistics of accesses to the table (for each table, postgres counts the number of seq scans and accesses through indexes). If the number of seq scans is greater than 0, it means that there is not enough index for some SELECT

To check, there is a diagnosis of "Tables with missing indexes"

It's like an alternative https://habr.com/ru/companies/tinkoff/articles/454066/

mfvanek commented 4 months ago

See also https://github.com/pganalyze/lint