From: | Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> |
---|---|
To: | Gregory Stark <stark(at)enterprisedb(dot)com> |
Cc: | PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: would it be a lot of work, to add optimizations accross unions ? |
Date: | 2009-02-28 11:49:07 |
Message-ID: | 3B5C1D4E-6149-4F03-A3B6-F35847B2EF0F@pointblue.com.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2009-02-28 13:31:28 | Re: would it be a lot of work, to add optimizations accross unions ? |
Previous Message | Gregory Stark | 2009-02-28 11:37:55 | Re: would it be a lot of work, to add optimizations accross unions ? |