On Aug 20, 2008, at 12:43 PM, Tom Lane wrote:
We have speculated in the past about having alternative plans that could be conditionally executed based on information not available at planning time. This could be seen as a first experiment in that direction. I am not thinking of a general-purpose AlternativePlan kind of execution node, because SubPlans aren't actually part of the main plan-node tree, but an AlternativeSubPlans expression node type might work.
Something I think we could also use is the ability to grab certain information before planing takes place. The big case that comes to mind is:
SELECT ... FROM big_table b JOIN small_lookup_table s USING (small_lookup_id)
WHERE s.some_name = 'alpha';
... or where we're doing s.some_name IN ('a','b','c'). In many cases,
translating the some_name lookup into actual _id values that you can
then look at in pg_stats for big_table results in a huge improvement
is rowcount estimates. If this is then joining to 5 other tables,
that rowcount information can have a huge impact on the query plan.
Another technique that we could play with is to have the AlternativeSubPlans node track the actual number of calls it gets, and switch from the "retail" implementation to the "hashed" implementation if that exceeds a threshold. This'd provide some robustness in the face of bad estimates, although of course it's not optimal compared to having made the right choice to start with.
In many systems, having the most optimal plan isn't that important; not having a really bad plan is. I expect that giving the executor the ability to decide the planner made a mistake and shift gears would go a long way to reducing the impact of bad plans. I wonder if any other databases have that ability... maybe this will be a first. :)
-- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
