Hi folks
While replying to another query, something struck me as interesting that
I thought I'd raise.
People here often raise issues where preparing a parameterised query and
executing the prepared query results in a plan that's sub-optimal for
the particular values substituted into it. The cause is well understood
- the planner has to pick a plan that looks good without knowledge of
what the value to be substituted in will be.
Things like pre-parsed prepared statements that're re-planned on every
execution are often proposed as solutions to this. This has me
wondering: rather than expensively re-planning from scratch, would it be
possiblet to adjust the planning process so that *multiple* alternative
plans would be cached for a query, using placeholders for unknown
rowcounts and costs? At execution, the unknown costs would be filled in
and the plans compared then the best plan picked for this execution. Is
this crazy talk, or could it significantly reduce the cost of
re-planning parameterized prepared statements to the point where it'd be
worth doing by default?
On an unrelated note, does Pg do any kind of smart searching on `IN'
lists, or just a linear scan? Would it be worth sorting longer IN list
results so each iteration could do a binary search of the list?
--
Craig Ringer