On 28 Feb 2009, at 11:37, Gregory Stark wrote:
Grzegorz Jaskiewicz <g...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
