There seems to be a few situations in which Postgres considers an "on average slightly better" plan over a "more predictable plan"

The failure mode of which can be the following:

SELECT min(number) FROM table WHERE filter = 'value'

(assuming a large enough table (~1M rows), indexes on number and filter separately)

If the filter is "not very selective" (10k, 1% of the table matches) but the values on the matches are mostly higher compared to the other 99% of the table - then postgres won't take that into account, and will instead assume that scanning an index on number will be faster (and get a match after on average ~100 rows assuming a uniform distribution of values). It will then effectively scan through 999k rows before getting a match.

The same query has an alternate plan - index scanning on filter=value which will require traversing all matches. If the filter is not very selective like before, this could be a terrible plan (1% of the table being traversed) but it is more predictable - it doesn't depend on the distribution of the values. Incidentally, if the filter becomes selective enough (aka, the planner anticipates few enough matches), then it will systematically use this plan.

In this case, intuitively I'd like the query planner to try out the "best" plan, up to the expected cost (say, a few hundred) - then give up on that plan and carry on with the more predictable plan, in order to hedge against the worst case scenario, and run the second plan.

Would that be possible, has it been considered in the past, and is there another solution to this kind of problem?

1 Answer 1


No. It would take a tremendous amount of machinery to be in place to do this, and just making the plan estimates better in the first place would probably be vastly easier than adding all that alternative-execution machinery. Also, plan estimates are not even calibrated to time, they are just in arbitrary units, so it wouldn't know when to give up. I guess you could give up based on row counts rather than time.

You could open two connections and run two queries asynchronously (suitably constructed/hinted to force the alternative plans), then cancel which ever one doesn't finish first. But generally if you know you are about to do something dangerous, you would just force it to use the "safer" plan all the time.

  • This “suitably hinted” thing you talk about, please tell us more. Postgres doesn’t have hints like Oracle does so maybe you mean something else. Sep 15, 2022 at 9:13
  • 1
    @Colin'tHart like Albe's answer to his previous question. But there also there are true planner hints, via an extension github.com/ossc-db/pg_hint_plan
    – jjanes
    Sep 15, 2022 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.