Обсуждение: would it be a lot of work, to add optimizations accross unions ?
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? 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 (of course this is just a simplified example).
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. > 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? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
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
Grzegorz Jaskiewicz <gj@pointblue.com.pl> writes: > On 28 Feb 2009, at 11:37, Gregory Stark wrote: >> >> 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. >> > I belive limit it self can't be pushed down, but with order by - why not ? Because my patch wasn't finished? There were still things about the planner I didn't understand which blocked me from finishing it at the time. >>> 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. No, I meant I don't understand what you're trying to do with this query or what you would propose the planner should do with it. Afaict this isn't a valid query at all and I don't see two queries to merge in it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!