Windowing Qual Pushdown
in [PgSql]
|
From: Hitoshi Harada on 23 Mar 2010 03:19 2010/3/21 Daniel Farina <drfarina(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Daniel Farina on 23 Mar 2010 03:22 On Tue, Mar 23, 2010 at 12:19 AM, Hitoshi Harada <umi.tanuki(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Hitoshi Harada on 23 Mar 2010 03:40 2010/3/23 Daniel Farina <drfarina(a)acm.org>: > On Tue, Mar 23, 2010 at 12:19 AM, Hitoshi Harada <umi.tanuki(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Daniel Farina on 23 Mar 2010 04:20 On Tue, Mar 23, 2010 at 12:40 AM, Hitoshi Harada <umi.tanuki(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Hitoshi Harada on 23 Mar 2010 10:01 2010/3/23 Daniel Farina <drfarina(a)acm.org>: > On Tue, Mar 23, 2010 at 12:40 AM, Hitoshi Harada <umi.tanuki(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
|
Next
|
Last
Pages: 1 2 Prev: Standalone backends run StartupXLOG in an incorrect environment Next: [HACKERS] Opa |