Обсуждение: WIP: parameterized function scan
Hello, following this short discussion http://archives.postgresql.org/message-id/4F5AA202.9020906@gmail.com I gave it one more try and hacked the optimizer so that function can become an inner relation in NL join, parametrized with values from the outer relation. I tried to explain my thoughts in comments. Other than that: 1. I haven't tried to use SpecialJoinInfo to constrain join order. Even if the order matters in query like SELECT * from a, func(a.i) it's still inner join by nature. SpecialJoinInfo is used for INNER join rarely, but never stored in PlannerInfo. Doing so only for these lateral functions would be rather disruptive. 2. Simple SQL function (i.e. one that gets pulled-up into the main query) is a special case. The query that results from such a pull-up no longer contains any function (and thus is not affected by this patch) but such cases need to be newly taken into account and examined / tested (the patch unblocks them at parsing stage too). 3. There might be some open questions about SQL conformance. I've spent quite a while looking into the optimizer code and after all I was surprised that it didn't require that many changes. At least to make few simple examples work. Do I ignore any important fact(s) ? Thanks, Tony.
Вложения
On Fri, May 11, 2012 at 5:52 PM, Antonin Houska <antonin.houska@gmail.com> wrote: > Hello, > following this short discussion > http://archives.postgresql.org/message-id/4F5AA202.9020906@gmail.com > I gave it one more try and hacked the optimizer so that function can become > an inner relation in NL join, parametrized with values from the outer > relation. > > I tried to explain my thoughts in comments. Other than that: > > 1. I haven't tried to use SpecialJoinInfo to constrain join order. Even if > the order matters in query like > SELECT * from a, func(a.i) > it's still inner join by nature. SpecialJoinInfo is used for INNER join > rarely, but never stored in PlannerInfo. Doing so only for these lateral > functions would be rather disruptive. > > 2. Simple SQL function (i.e. one that gets pulled-up into the main query) is > a special case. The query that results from such a pull-up no longer > contains any function (and thus is not affected by this patch) but such > cases need to be newly taken into account and examined / tested (the patch > unblocks them at parsing stage too). > > 3. There might be some open questions about SQL conformance. > > I've spent quite a while looking into the optimizer code and after all I was > surprised that it didn't require that many changes. At least to make few > simple examples work. Do I ignore any important fact(s) ? This implementation looks different than I'd expect: I would have thought that it would work by generating paths with param_info set to the appropriate set of rels to provide the necessary values, rather than inventing its own mechanism for forcing a nestloop. Also, I think we will want something that implements the LATERAL() syntax, rather than just removing the prohibition on lateral references. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 05/22/2012 09:31 PM, Robert Haas wrote: > This implementation looks different than I'd expect: I would have > thought that it would work by generating paths with param_info set to > the appropriate set of rels to provide the necessary values, rather > than inventing its own mechanism for forcing a nestloop. My consideration was something like: clauses are responsible for the fact that parameter info is path-specific. However function parameters have little to do with clauses, so they should be treated rather separate, at higher level than that of individual paths. Looking at the code again, I admit there's yet no strong reason not to use the existing 'parameterization infrastructure'. I'll try to think up something better. > Also, I think we will want something that implements the LATERAL() > syntax, rather than just removing the prohibition on lateral references. So you think it's not good to first implement (implicitly) lateral functions and add the explicit LATERAL() operator later, when the optimizer can handle lateral subqueries too? (explicit LATERAL() that only can take function as the argument and not subquery sounds weird). I personally need the lateral functions much more than lateral subqueries, but I have no impression of the attitude of (majority of) other users. While I believe I'm (slowly though) approaching a viable concept of lateral functions, it's quite clear to me that the lateral subqueries are obviously a bigger challenge. These are both the reasons why I started to think about the functions as a separate patch. Tony H.
Antonin Houska <antonin.houska@gmail.com> writes: > On 05/22/2012 09:31 PM, Robert Haas wrote: >> Also, I think we will want something that implements the LATERAL() >> syntax, rather than just removing the prohibition on lateral references. > So you think it's not good to first implement (implicitly) lateral > functions and add the explicit LATERAL() operator later, when the > optimizer can handle lateral subqueries too? Well, it's not per spec: what you did accepts queries that are invalid per spec and are very likely to be errors rather than intentional invocations of the LATERAL facility. This might be all right for experimental playing around, but there is zero chance that we will commit it that way. Quite aside from the error-detection issue, I would think that we would want explicit LATERAL JOIN syntax which we'd turn into join order restrictions (cf SpecialJoinInfo), so that the planner need not waste time searching join orders that can't possibly work because of the presence of the parameter references. This situation is unlike the standard nestloop-with-parameterized-inner-scan case, because in that case the parameterized path is merely one option of many. With a LATERAL reference, there are no other implementation options. > I personally need the lateral functions much more than lateral > subqueries, but I have no impression of the attitude of (majority of) > other users. > While I believe I'm (slowly though) approaching a viable concept of > lateral functions, it's quite clear to me that the lateral subqueries > are obviously a bigger challenge. > These are both the reasons why I started to think about the functions > as a separate patch. Quite honestly, I think that something that only handles functions as LATERAL righthands is broken by design. It doesn't meet the spec, and it's unlikely to represent much of a step towards a full implementation. Remember Polya's Inventor's Paradox: the more general problem may be easier to solve. regards, tom lane
On 05/24/2012 12:46 AM, Tom Lane wrote: > Well, it's not per spec: what you did accepts queries that are invalid > per spec and are very likely to be errors rather than intentional > invocations of the LATERAL facility. This might be all right for > I think I saw queries where function is joined with no explicit LATERAL(). Nevertheless... > Quite honestly, I think that something that only handles functions as > LATERAL righthands is broken by design. It doesn't meet the spec, and > it's unlikely to represent much of a step towards a full implementation. > Remember Polya's Inventor's Paradox: the more general problem may be > easier to solve. ... sounds more serious. I'll keep it in mind if I get the impression that I have a new idea about this problem anytime. Thanks, Tony H.