Postgres Pro
Downloads
Home   >   mailing lists

Thread: Adaptive query execution

Adaptive query execution

From
Tim Kane
Date:

Hi all,

So I was thinking about the following, after experimenting with constraint exclusion.

I thought I would see what happens when I do this:

  SELECT * FROM ONLY table_a UNION SELECT * FROM table_b;


I noticed that despite table_a still having no data in it, the planner has already decided that it needs to insert a chain of  ‘append->sort->unique’  nodes into the plan.

That’s fairly reasonable.
While I understand that we can’t readily know about wether a given node will return anything or not - would it be possible to have the execution engine branch off in the event that a given node returns nothing at all?

I guess there are probably a lot of considerations, and I suspect it would considerably increase planning time, though maybe it also presents an opportunity for some interesting approaches to adaptive query execution.

I don’t know so much about this, though I’m sure there are all kinds of research papers discussing it.
Is this something that has been considered before?

Tim

Re: Adaptive query execution

From
Claudio Freire
Date:
On Tue, May 13, 2014 at 5:08 PM, Tim Kane <tim.kane@gmail.com> wrote:
> Hi all,
>
> So I was thinking about the following, after experimenting with constraint
> exclusion.
>
> I thought I would see what happens when I do this:
>
>   SELECT * FROM ONLY table_a UNION SELECT * FROM table_b;
>
>
> I noticed that despite table_a still having no data in it, the planner has
> already decided that it needs to insert a chain of  ‘append->sort->unique’
> nodes into the plan.
>
> That’s fairly reasonable.
> While I understand that we can’t readily know about wether a given node will
> return anything or not - would it be possible to have the execution engine
> branch off in the event that a given node returns nothing at all?
>
> I guess there are probably a lot of considerations, and I suspect it would
> considerably increase planning time, though maybe it also presents an
> opportunity for some interesting approaches to adaptive query execution.


What's the point, in the context of this example?

The sort-unique still has to be performed even if you didn't have data
in one side, since the other could still have duplicates.

Re: Adaptive query execution

From
Tim Kane
Date:


From: Claudio Freire <klaussfreire@gmail.com>
I thought I would see what happens when I do this:

   SELECT * FROM ONLY table_a UNION SELECT * FROM table_b;



What's the point, in the context of this example?

The sort-unique still has to be performed even if you didn't have data
in one side, since the other could still have duplicates.


Damn it. Okay, bad example. I should sleep.

A better example would be (from my other post just now - http://www.postgresql.org/message-id/CF9838D8.7EF3D%tim.kane@gmail.com ) where an empty parent-table is append’ed into a result set involving one (and only one) of its child relations.  Whereas a more optimum solution would involve only the child relation without the need to append the empty parent relation.

I’m sure there are other scenarios where adaptive query execution would be of greater benefit.

Tim