From: | Decibel! <decibel(at)decibel(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: Improving non-joinable EXISTS subqueries |
Date: | 2008-08-22 04:28:48 |
Message-ID: | 06E8337A-350D-4C5E-98CF-758C1123CD05@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2008-08-22 04:41:30 | Re: proposal sql: labeled function params |
Previous Message | Decibel! | 2008-08-22 04:14:04 | Re: Patch: plan invalidation vs stored procedures |