Does maintaining more specific partial indices cost more to maintain than a consolidated general index?

user_2807904 0
Viewed 31

Say I have this table

CREATE TABLE users(
  id INT, 
  name TEXT,
  state TEXT,
  needs_advice TEXT
)

and there are 2 usage patterns

  • querying users who need financial advice
  • querying users who need relationship advice

and I'm thinking of adding these 2 partial indices or 1 generic index

CREATE INDEX idx_users_needs_financial_advice
  ON users(state, needs_advice)
  WHERE needs_advice = 'financial'
  AND state = 'help'

CREATE INDEX idx_users_needs_relationship_advice
  ON users(state, needs_advice)
  WHERE needs_advice = 'relationship'
  AND state = 'help'

and using a more generic index below

CREATE INDEX idx_users_needs_relationship_advice
  ON users(state, needs_advice)

What would the tradeoffs be between using these 2 partial indices versus the 1 more generic index? My initial impression is the 2 partial indices require less maintenance than the 1 generic index but I'm not sure.

postgresql
1 Answers

The maintenance costs for rows that need to be indexed would be the same, but the more indexes you have the harder the optimizer has to think, so I'd expect that to be a loss.

On the other hand, it is nice to only have an index on the rows that you need, so that the index is smaller and fewer rows require index maintenance.

You can have the best of both worlds with this index:

CREATE INDEX ON users (needs_advice)
WHERE needs_advice IN ('financial', 'relationship')
  AND state = 'help';

Adding state to the index is unnecessary, unless you want to have an index-only scan (and then you would probably have to add more columns).

user_64643080
Related