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(at)pointblue(dot)com(dot)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