Обсуждение: Windowing Qual Pushdown
In the function "subquery_is_pushdown_safe", there is an immediate "false" returned if the subquery has a windowing function. While that seems true in general, are there cases where we can push down a qual if it is on the partitioning key? Or do NULLs or some other detail get in the way? fdr
2010/3/21 Daniel Farina <drfarina@acm.org>: > In the function "subquery_is_pushdown_safe", there is an immediate > "false" returned if the subquery has a windowing function. While that > seems true in general, are there cases where we can push down a qual > if it is on the partitioning key? Or do NULLs or some other detail > get in the way? Ugh, that seems true. In similar case you can push down WHERE clause of outer query to subquery if the qual clause match GROUP BY clause. This is done by transforming outer WHERE -> HAVING -> inner WHERE. However, window function querys don't have such clause as HAVING of aggregate. If you implement that optimization, we need have kind of implicit, homologous qual information. Sure, it's possible. Regards, -- Hitoshi Harada
On Tue, Mar 23, 2010 at 12:19 AM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: > If you implement that optimization, we need have kind of > implicit, homologous qual information. Sure, it's possible. I'm not sure precisely what you mean here. Do you predict the mechanism will be complicated? It's been a burning itch of mine for a little while now. I do not know exactly how windowing functions look in Query values just yet, although I'm very familiar with older structures there. fdr
2010/3/23 Daniel Farina <drfarina@acm.org>: > On Tue, Mar 23, 2010 at 12:19 AM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: >> If you implement that optimization, we need have kind of >> implicit, homologous qual information. Sure, it's possible. > > I'm not sure precisely what you mean here. Do you predict the > mechanism will be complicated? It's been a burning itch of mine for a > little while now. I do not know exactly how windowing functions look > in Query values just yet, although I'm very familiar with older > structures there. I believe the changes will probably not be 2-3 lines (ie. a member added to Query structure, etc) if I try it. But the optimizer part is too complicated to me so that I am not sure, either. My idea above is that the similar mechanism you see in GROUP BY optimization will help you and the issue is not so particular about window functions. Regards, -- Hitoshi Harada
On Tue, Mar 23, 2010 at 12:40 AM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: > I believe the changes will probably not be 2-3 lines (ie. a member > added to Query structure, etc) if I try it. But the optimizer part is > too complicated to me so that I am not sure, either. My idea above is > that the similar mechanism you see in GROUP BY optimization will help Are you suggesting that the windowing clause should perhaps refer to a column in the target list, much like GROUP BY/ORDER BY, so that one can easily see if the qual in the fromexpr corresponds to the windowClause to see if the pushdown is safe? fdr
2010/3/23 Daniel Farina <drfarina@acm.org>: > On Tue, Mar 23, 2010 at 12:40 AM, Hitoshi Harada <umi.tanuki@gmail.com> wrote: >> I believe the changes will probably not be 2-3 lines (ie. a member >> added to Query structure, etc) if I try it. But the optimizer part is >> too complicated to me so that I am not sure, either. My idea above is >> that the similar mechanism you see in GROUP BY optimization will help > > Are you suggesting that the windowing clause should perhaps refer to a > column in the target list, much like GROUP BY/ORDER BY, so that one > can easily see if the qual in the fromexpr corresponds to the > windowClause to see if the pushdown is safe? The windowing clause refer to the targetlist as resjunk columns. I thought we need some intermediate data like havingQual to tell what is pushed down to subquery, because the pushdown of GROUP BY columns is done later in subquery_planner() of the subquery (as pushed-down havingQual), not in set_subquery_pathlist(). However, I found the real problem. If the query has multiple window definitions, at this stage you cannot tell if the pushdown is safe nor cannot know how, because the order of evaluation of individual window is decided later in grouping_planner(). So the workaround is to limit this optimization for only one window definition case but it seems too narrow solution. Maybe there're chances in setrefs.c to do it after grouping_planner(), but I'm not quite sure. Regards, -- Hitoshi Harada
Hitoshi Harada <umi.tanuki@gmail.com> writes:
> I believe the changes will probably not be 2-3 lines (ie. a member
> added to Query structure, etc) if I try it. But the optimizer part is
> too complicated to me so that I am not sure, either. My idea above is
> that the similar mechanism you see in GROUP BY optimization will help
> you and the issue is not so particular about window functions.
The real question is what benefit you expect to get. If the filter
condition can't be pushed below the window functions (which AFAICS it
can't without changing the results) then there is really nothing to be
gained compared to leaving it in the outer query level.
regards, tom lane
On Tue, Mar 23, 2010 at 8:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The real question is what benefit you expect to get. If the filter > condition can't be pushed below the window functions (which AFAICS Even on the partition key? Right now if you define a view with a windowing + PARTITION BY clause in it and people write a lot of queries to interrogate one partition or the other, you end up computing results for the entire relation, and then filtering all but one partition out, in my understanding. Since it seems on the surface there is no context sensitivity(?) between partitions in this kind of a case it would seem a qual pushdown on the partition key would help rather intensely. fdr