Re: Improving non-joinable EXISTS subqueries

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  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828