4 ms·

I had a crazy case where after adding indexes to things, queries suddenly become much much slower. I had to look over the plan and realized that removing these

I had a crazy case where after adding indexes to things, queries suddenly become much much slower. I had to look over the plan and realized that removing these indexes made queries fast again. Turns out you can't just take for granted that an index will speed things up and its something you actually have to test and record before/after results. I'm still slightly at a loss as to what the issue was but the best I can find is that if you index something with lots of duplicate values, you make the query slower as it has to scan the index and then scan the long list of results.

scns 5y ago
Thank you for sharing, something to remember, a valuable lesson.
polynox 5y ago
Indices work best when the number of matching entries is low. There is a metric called index selectivity which is the number of distinct values of the indexed columns divided by the total number of records. For a boolean value, this would be 2/N records, effectively the worst possible index. For a perfect index, it would be 1 (every row has a unique entry in the index). It could be possible for the query planner to get the answer wrong for which index to use if it happens to be wrong about the selectivity of your particular query, because the selectivity must be approximated. See for example PostgreSQL's (a fantastic database) documentation on these approximations and imagine a number of ways it could fail [1]. > Assuming a linear distribution of values inside each bucket, [...] > This amounts to assuming that the fraction of the column that is not any of the MCVs is evenly distributed among all the other distinct values. > Using some rather cheesy assumptions about the frequency of different characters [1] https://www.postgresql.org/docs/current/row-estimation-examples.html https://www.postgresql.org/docs/current/row-estimation-examp...

scns 5y ago

Thank you for sharing, something to remember, a valuable lesson.

polynox 5y ago

Indices work best when the number of matching entries is low. There is a metric called index selectivity which is the number of distinct values of the indexed columns divided by the total number of records. For a boolean value, this would be 2/N records, effectively the worst possible index. For a perfect index, it would be 1 (every row has a unique entry in the index). It could be possible for the query planner to get the answer wrong for which index to use if it happens to be wrong about the selectivity of your particular query, because the selectivity must be approximated. See for example PostgreSQL's (a fantastic database) documentation on these approximations and imagine a number of ways it could fail [1]. > Assuming a linear distribution of values inside each bucket, [...] > This amounts to assuming that the fraction of the column that is not any of the MCVs is evenly distributed among all the other distinct values. > Using some rather cheesy assumptions about the frequency of different characters [1] https://www.postgresql.org/docs/current/row-estimation-examples.html https://www.postgresql.org/docs/current/row-estimation-examp...