Re: would it be a lot of work, to add optimizations accross unions ?

On 28 Feb 2009, at 11:37, Gregory Stark wrote:

>
> Grzegorz Jaskiewicz <gj@pointblue.com.pl> writes:
>
>> Say I have:
>>
>> select foo (
>> select foo from bar1
>>  union all
>> select foo from bar2
>>  union all
>> select foo from bar3
>>  ...
>> ) a order by foo desc limit X;
>>
>>
>> (and I can give you few other examples around the same 'note', say  
>> with when
>> foo=N in outer subselect)
>>
>> Would anyone consider such optimization, when postgres will apply  
>> the same
>> condition to inner queries, providing that their size is substantial?
>
> Well you haven't said what optimization you're looking for here.
>
> I posted a patch to look for an ordered path for members of a union  
> a while
> back but it still needed a fair amount of work before it was usable.
>
> The LIMIT can't be pushed into the union unless we do have ordered  
> paths so
> that's further down the line. (And even then it would only work if  
> there are
> no conditions on there append path.)
>
> And I believe we already do push down where clauses like foo=N.

ok, in this case - I have few tables, few M rows each - and I need to  
find out highest id - for instance. doing it like that, would cause  
postgres to seq scan all of them, one by one. Obviously, I could just  
rewrite subselects, to include order+limit, but I was wondering,  
whether that job couldn't be done on planner's level.
I belive limit it self can't be pushed down, but with order by - why  
not ? if you look for X results, limiting all selects to X, will  
guarantee the same result - providing of course it is used with order  
by. (which, in my opinion - should be illegal without order by, anyway).


>
>> Same would actually apply for different subqueries, without union/
>> intersect/etc:
>>
>> select foo( select foo from bar1 ) a where foo in (x,y,z) order by  
>> foo  desc
>> limit N
>
> huh?
Just a simple example, perhaps oversimplified.
The thing is, in case like that - planner could merge two queries  
together.


thanks Gregory.

-- 
GJ