We avoid functional indexes and refuse to add an index for every slow query. A vendor-generated Keycloak query took 1.1 s and 2.27 million buffer hits to return zero rows . After checking every cheaper option, we added one (name, lower(value)) index. Result: 0.2 ms and 11 buffers. Below is why the rule exists, why we broke it, and the checklist we now use for exceptions.
The rules
Below are a couple of rules we rarely break:
No function-based (expression) indexes.
No new index for every slow query.
Last week we broke both, on purpose, for one index on a table we don't own.
Why we have them
Every index has a cost on every write , not only on the read it was built for. Each INSERT and each UPDATE to an indexed column means another B-tree insertion, more WAL, more vacuum work and more storage. One index is cheap. Thirty indexes on a busy table are not, and that's where you end up if every slow query gets its own index.
Functional indexes add their own problems:
They only match an identical expression.…