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