Menu

1 / 5

We Don't Do Functional Indexes. Here's Why We Made One Anyway.

DEV Community: database·Pranay Ravi·1 day ago
#UguVeKLg
#devtodatabase#devto#dev#index#rows
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.