Hacker News Reader

While I don’t recall running into issues either, I can certainly see that a new index could cause the query planner to make a different decision. And that decision could - in some cases - end up being worse that the previous behaviour.

I definitely have seen the query planing make some peculiar choices in the past.

View on HN

This is what I ran into. Often times they were indexes with a similar cost as another, and that caused issues.

I think the main index type that bit me are the ones created by exclusion constraints. Often times it looks to the planner like "the right" index to use, but there is another (btree) that is way cheaper...the exclusion constraint is just there to ensure consistency.

In those cases to fix things, I added a WHERE clause to the index (e.g. WHERE 1=1), and the planner wouldn't consider that index unless it saw that same 1=1 condition in the queries WHERE clause.

Reply on news.ycombinator.com