Some time ago, I wrote:
> I've been studying the bug reported at
> http://www.postgresql.org/message-id/20130617235236.GA1636@jeremyevans.local
> ...
> After some contemplation, I think that the most practical way to fix
> this is for deconstruct_recurse and distribute_qual_to_rels to
> effectively
Antonin Houska writes:
> On 07/04/2013 06:11 PM, Antonin Houska wrote:
>> On 07/03/2013 08:32 PM, Tom Lane wrote:
>>> Another possibility would be to keep the optimization, but disable it in
>>> queries that use LATERAL. I don't much care for that though --- seems
>>> too Rube Goldbergish, and in
Ashutosh Bapat writes:
> On Wed, Jun 26, 2013 at 1:30 AM, Tom Lane wrote:
>> For there to *be* a unique "appropriate outer join", we need to require
>> that a LATERAL-using qual clause that's under an outer join contain
>> lateral references only to the outer side of the nearest enclosing outer
>
I have couple of questions.
On Wed, Jun 26, 2013 at 1:30 AM, Tom Lane wrote:
> I've been studying the bug reported at
>
> http://www.postgresql.org/message-id/20130617235236.GA1636@jeremyevans.local
> that the planner can do the wrong thing with queries like
>
> SELECT * FROM
> i LEFT JOIN LAT
On 07/04/2013 06:11 PM, Antonin Houska wrote:
On 07/03/2013 08:32 PM, Tom Lane wrote:
Another possibility would be to keep the optimization, but disable it in
queries that use LATERAL. I don't much care for that though --- seems
too Rube Goldbergish, and in any case I have a lot less faith in t
On 07/03/2013 08:32 PM, Tom Lane wrote:
Another possibility would be to keep the optimization, but disable it in
queries that use LATERAL. I don't much care for that though --- seems
too Rube Goldbergish, and in any case I have a lot less faith in the
whole concept now than I had before I starte
I wrote:
> So attached is a draft patch for this. It's not complete yet because
> there are various comments that are now wrong and need to be updated;
> but I think the code is functioning correctly.
Hm, spoke too soon :-(. This query causes an assertion failure, with or
without my draft patch:
Antonin Houska writes:
> If the WHERE clause moves up, then the resulting plan might be less
> efficient than the one we'd get if the subquery hadn't been pulled-up at
> all.
No, because we can push the qual back down again (using a parameterized
path) if that's appropriate. The problem at thi
On 06/26/2013 12:52 AM, Tom Lane wrote:
Instead of setting it aside, can we (mis)use placeholder var (PHV), to
ensure that the WHERE clause is evaluated below the OJ; instead of
combining it with the ON clause?
No, that doesn't help; it has to be part of the joinquals at the join
node, or you do
Antonin Houska writes:
> On 06/25/2013 10:00 PM, Tom Lane wrote:
>> After some contemplation, I think that the most practical way to fix
>> this is for deconstruct_recurse and distribute_qual_to_rels to
>> effectively move such a qual to the place where it logically belongs;
>> that is, rather tha
(Please excuse me if my proposal sounds silly, i'm still not too
advanced in this area...)
On 06/25/2013 10:00 PM, Tom Lane wrote:
After some contemplation, I think that the most practical way to fix
this is for deconstruct_recurse and distribute_qual_to_rels to
effectively move such a qual to
I've been studying the bug reported at
http://www.postgresql.org/message-id/20130617235236.GA1636@jeremyevans.local
that the planner can do the wrong thing with queries like
SELECT * FROM
i LEFT JOIN LATERAL (SELECT * FROM j WHERE i.n = j.n) j ON true;
I think the fundamental problem is that, b
David Fetter writes:
> Please find attached a patch which implements approach 3. The vast
> majority of it is changes to the regression tests. The removed
> regression tests in join.{sql,out} are no longer errors, although some
> of them are pretty standard DoS attacks, hence they're all removed
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)
> Actually, this appears to fail already, at least in 9.2.2:
> => select * from (values (1)) v(a) where v.a in (select x from (values (2))
> v2(a),
> ->
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)
Actually, this appears to fail already, at least in 9.2.2:
=> select * from (values (1)) v(a) where v.a in (select x from (values (2))
v2(a),
-> generate_series(1,a)
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> However ... David is wrong to claim that it's zero-risk. It's true that
> an SRF can't contain any side-references today, but it can contain an
> outer reference. Consider a case like
>
> SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...
Stephen Frost writes:
> * David Fetter (da...@fetter.org) wrote:
>> 3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
>>
>> (As far as I can tell, those cases whose behaviour would be changed by
>> this actually produce errors in versions prior to 9.3, so no working
>> code should
* David Fetter (da...@fetter.org) wrote:
> As I see it, the current options are:
>
> 1. Do nothing, and insist on non-standard use of the LATERAL keyword.
I'm not a big fan of this. Providing a good error message saying "you
need to use LATERAL for this query to work" makes it slightly better,
b
On Thu, Jan 24, 2013 at 09:12:41PM -0800, David Fetter wrote:
> On Thu, Jan 24, 2013 at 09:51:46AM -0800, David Fetter wrote:
> > Folks,
> >
> > Andrew Gierth asked me to send this out as his email is in a parlous
> > state at the moment. My comments will follow in replies. Without
> > further a
Folks,
Andrew Gierth asked me to send this out as his email is in a parlous
state at the moment. My comments will follow in replies. Without
further ado:
SQL2008 says, for 7.6
6)
a) If TR is contained in a FC with no intervening , then the scope clause SC of TR is the or innermost that
2012/12/15 Tom Lane :
> Pavel Stehule writes:
>> Is this behave expected?
>
>> -- unexpected
>> postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT
>> random()) x;
>> ;
>> vrandom
>> ---+--
>> 1 0.63025646051392
>> 2 0.63025646051392
>> 3 0.630256
Pavel Stehule writes:
> Is this behave expected?
> -- unexpected
> postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT random())
> x;
> ;
> vrandom
> ---+--
> 1 0.63025646051392
> 2 0.63025646051392
> 3 0.63025646051392
> (3 rows)
The LATERAL ke
Hello
I tested some usage of LATERAL clause, and I found so LATERAL doesn't
respects difference between VOLATILE and IMMUTABLE functions.
Is this behave expected?
-- unexpected
postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT random()) x;
;
v │ random
───┼───
On 03/10/2012 02:15 AM, Tom Lane wrote:
Um ... how do you get the subquery result rows to join to only the
correct rows of the other tables?
The subquery just restricts the set of rows that the function has to
evaluate. The main query is supposed to perform the join.
I understand, such a join ca
On Sat, Mar 10, 2012 at 4:16 AM, Tom Lane wrote:
> Robert Haas writes:
>> On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane wrote:
>>> Um ... how do you get the subquery result rows to join to only the
>>> correct rows of the other tables? This looks like an unconstrained join
>>> to me, which is not wh
Robert Haas writes:
> On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane wrote:
>> Um ... how do you get the subquery result rows to join to only the
>> correct rows of the other tables? This looks like an unconstrained join
>> to me, which is not what I believe the SQL spec for LATERAL to be, and
>> it d
On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane wrote:
> Antonin Houska writes:
>> For example:
>
>> SELECT *
>> FROM a, b, func(a.i, b.j) as c, d
>> WHERE a.i=b.j and b.j = d.k and c>1
>
>> may become
>
>> SELECT *
>> FROM a, b, as c, d
>> WHERE a.i=b.j and b.j = d.k and c>1
>
>> where is
>
>> SELE
Antonin Houska writes:
> For example:
> SELECT *
> FROM a, b, func(a.i, b.j) as c, d
> WHERE a.i=b.j and b.j = d.k and c>1
> may become
> SELECT *
> FROM a, b, as c, d
> WHERE a.i=b.j and b.j = d.k and c>1
> where is
> SELECT func(a.i, b.j)
> FROM a,b
> WHERE a.i=b.j
Um ... how do you ge
Hello,
in the related discussions mentioned on TODO list
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00292.php
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00991.php
(The 1st is rather on SQL, I didn't focuss on it yet.)
the implementation is discussed from optimizer/execu
Robert Haas wrote:
> On Sat, Dec 19, 2009 at 11:01 PM, Robert Haas wrote:
> > On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane wrote:
> >> Robert Haas writes:
> >>> On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane wrote:
> I believe the correct approach is probably to treat values that need to
> be
Robert Haas writes:
>> Yeah, this is grotty. It appears that the comment introducing
>> ExecReScan() is somewhat incorrect. It asserts that exprCtxt is used
>> only
> Sigh.
> ...is used only for index scans. However, it's actually also used for
> bitmap scans (both heap and index) and TID sca
On Sat, Dec 19, 2009 at 11:01 PM, Robert Haas wrote:
> On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane wrote:
>> Robert Haas writes:
>>> On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane wrote:
I believe the correct approach is probably to treat values that need to
be propagated into the inner side
On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane wrote:
>>> I believe the correct approach is probably to treat values that need to
>>> be propagated into the inner side as executor parameters. This could
>>> replace the existin
Robert Haas writes:
> On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane wrote:
>> I believe the correct approach is probably to treat values that need to
>> be propagated into the inner side as executor parameters. This could
>> replace the existing, rather crocky, management of values passed into a
>>
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane wrote:
> Robert Haas writes:
>> Incidentally, the reason why the executor chokes trying to execute a
>> SRF with an outer reference is because ExecEvalVar() craps out trying
>> to dereference a null TupleTableSlot. If I'm understanding this
>> correctly,
Robert Haas writes:
> Incidentally, the reason why the executor chokes trying to execute a
> SRF with an outer reference is because ExecEvalVar() craps out trying
> to dereference a null TupleTableSlot. If I'm understanding this
> correctly, that, in turn, happens because the variable that we're
On Thu, Dec 17, 2009 at 10:13 PM, Robert Haas wrote:
> Another question I have - while generalizing the inner-indexscan
> machinery is an interesting join optimization technique, I'm thinking
> that it actually has very little to do with LATERAL. Is there any
> reason to suppose that one or the o
2009/12/20 Robert Haas :
> On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada wrote:
>> 2009/10/20 Andrew Gierth :
>>> Right now, the only way pg can plan this is to do a hashjoin or
>>> mergejoin of the _entire content of big1 and big2_ and join the
>>> result against "small" (again in a hashjoin o
On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada wrote:
> 2009/10/20 Andrew Gierth :
>> Right now, the only way pg can plan this is to do a hashjoin or
>> mergejoin of the _entire content of big1 and big2_ and join the
>> result against "small" (again in a hashjoin or mergejoin plan).
>> This beco
2009/10/20 Andrew Gierth :
> Right now, the only way pg can plan this is to do a hashjoin or
> mergejoin of the _entire content of big1 and big2_ and join the
> result against "small" (again in a hashjoin or mergejoin plan).
> This becomes excessively slow compared to the "ideal" plan:
>
> nested
On Sun, Oct 18, 2009 at 2:57 PM, Tom Lane wrote:
> Robert Haas writes:
>> You could probably convince me that a merge join is not going to be
>> too useful (how often can you want a merge join on the inner side of a
>> nested loop?
>
> Why not? As Andrew pointed out, what we're really trying to
> "Greg" == Greg Stark writes:
>> Why not? As Andrew pointed out, what we're really trying to
>> accomplish here is consider sub-join plans that are parameterized
>> by a value obtained from an outer relation. I think we shouldn't
>> artificially limit what we consider.
Greg> Am I und
Greg Stark writes:
> nested loop
> index scan expecting 1 record
> merge join
> index scan on where col1 = outer.foo and col2
> between a and b
> some other scan
> Ie, where the nested loop is a degenerate nested loop which only
> expects a single value and provides a par
On Sun, Oct 18, 2009 at 12:57 PM, Tom Lane wrote:
> Robert Haas writes:
>> You could probably convince me that a merge join is not going to be
>> too useful (how often can you want a merge join on the inner side of a
>> nested loop?
>
> Why not? As Andrew pointed out, what we're really trying to
On Sun, Oct 18, 2009 at 3:57 PM, Tom Lane wrote:
> Robert Haas writes:
>> You could probably convince me that a merge join is not going to be
>> too useful (how often can you want a merge join on the inner side of a
>> nested loop?
>
> Why not? As Andrew pointed out, what we're really trying to
Robert Haas writes:
> You could probably convince me that a merge join is not going to be
> too useful (how often can you want a merge join on the inner side of a
> nested loop?
Why not? As Andrew pointed out, what we're really trying to accomplish
here is consider sub-join plans that are parame
On Sun, Oct 18, 2009 at 11:30 AM, Tom Lane wrote:
> Robert Haas writes:
>> I think you should only ever join an incomplete inner-indexscan path
>> to (1) another inner-indexscan path or (2) the cheapest total path for
>> *exactly* the future-join requirement. Anything else doesn't seem
>> produc
Robert Haas writes:
> I think you should only ever join an incomplete inner-indexscan path
> to (1) another inner-indexscan path or (2) the cheapest total path for
> *exactly* the future-join requirement. Anything else doesn't seem
> productive.
I don't see any reason to constrain the form of jo
On Sat, Oct 17, 2009 at 10:09 PM, Tom Lane wrote:
> Robert Haas writes:
>> That still leaves a lot of silly paths, though. In many cases, if
>> you're thinking about joining A to {B C} using an index-accelerated
>> path, you'd be just as well off joining to B first and then to C. So
>> it might
Robert Haas writes:
> That still leaves a lot of silly paths, though. In many cases, if
> you're thinking about joining A to {B C} using an index-accelerated
> path, you'd be just as well off joining to B first and then to C. So
> it might be that we only need to consider index-accelerated paths
Robert Haas writes:
> Another thought, only semi-related. One of the big use cases for
> LATERAL in general is to use a set-returning function in the FROM
> clause that uses vars from a preceding FROM item. I am idly wondering
> if there's a reason why ExecProject is not its own node type.
You
On Tue, Sep 22, 2009 at 11:21 PM, Tom Lane wrote:
>> Currently, however, we only consider this possibility when the inner
>> rel is NOT a joinrel. It seems like it might be possible to change
>> this, but it doesn't look straightforward.
>
> Well, it's straightforward enough in theory, it's just
Robert Haas writes:
> I've been turning this one over in my head. It seems to me that this
> is very similar to what we already do with inner index-scans, only
> generalized to joinrels.
Right.
> Currently, however, we only consider this possibility when the inner
> rel is NOT a joinrel. It se
On Wed, Sep 9, 2009 at 11:25 PM, Andrew Gierth
wrote:
> >> 4. LATERAL allows some optimizations that aren't currently done, either
> >> by explicitly rewriting the query, or (in theory) the optimizer itself
> >> could consider a lateral plan (I believe Oracle does this). This would
> >> apply
> "Robert" == Robert Haas writes:
>> Just to pick up on some points from the discussion:
>>
>> 1. LATERAL has to be explicit because it changes the scope of
>> references. For example, in:
>> ... (select ... FROM (select a AS b), (select b)) ...
>> the "b" in the second subselect coul
On Tue, Sep 8, 2009 at 6:29 PM, Andrew
Gierth wrote:
>> "David" == David Fetter writes:
>
> >> I've attempted to search the archives for references to the SQL
> >> LATERAL feature, which AIUI is fairly-frequently requested.
> >> [snip]
> >> Has anyone poked at this at all?
>
> David> I be
> "David" == David Fetter writes:
>> I've attempted to search the archives for references to the SQL
>> LATERAL feature, which AIUI is fairly-frequently requested.
>> [snip]
>> Has anyone poked at this at all?
David> I believe Andrew (RhodiumToad) Gierth is taking a look at
David> impl
On mån, 2009-09-07 at 19:06 -0400, Robert Haas wrote:
> On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentraut wrote:
> > Because joins can be reordered, whereas LATERAL creates a kind of
> > syntactic sequence point for join reordering. To pick up your example:
> >
> >> But this doesn't [work]:
> >>
>
On Mon, Sep 7, 2009 at 10:08 PM, Stephen Frost wrote:
> * Robert Haas (robertmh...@gmail.com) wrote:
>> Fair enough. I think I started to drift off in the direction of
>> making that argument, but it wasn't really my point.
>
> To be honest, I'm not sure I agree with Tom here on the value of
> req
* Robert Haas (robertmh...@gmail.com) wrote:
> Fair enough. I think I started to drift off in the direction of
> making that argument, but it wasn't really my point.
To be honest, I'm not sure I agree with Tom here on the value of
requiring a keyword to tell the system that you really mean what
On Mon, Sep 7, 2009 at 9:12 PM, Stephen Frost wrote:
> Robert,
>
> * Robert Haas (robertmh...@gmail.com) wrote:
>> On Mon, Sep 7, 2009 at 7:47 PM, Tom Lane wrote:
>> > Because as often as not, they're mistakes. Please don't think
>> > you're smarter than the spec here.
>>
>> You're frequently the
Robert,
* Robert Haas (robertmh...@gmail.com) wrote:
> On Mon, Sep 7, 2009 at 7:47 PM, Tom Lane wrote:
> > Because as often as not, they're mistakes. Please don't think
> > you're smarter than the spec here.
>
> You're frequently the first to criticize the spec, but I have no
> interest in secon
Robert Haas escribió:
> I haven't got a copy of the spec, so that's a bit of a handicap. If
> someone who does can look this up and comment on how it's supposed to
> work, I would certainly appreciate that. My understanding of it is
> currently based on random articles cherry-picked around the I
On Mon, Sep 7, 2009 at 7:47 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentraut wrote:
>>> You could argue that the parser could infer the references and the
>>> resultant join ordering restrictions automatically, but perhaps it was
>>> deemed that an expl
Robert Haas writes:
> On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentraut wrote:
>> You could argue that the parser could infer the references and the
>> resultant join ordering restrictions automatically, but perhaps it was
>> deemed that an explicit specification would be less error-prone.
> Well,
On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentraut wrote:
> On Sun, 2009-09-06 at 23:59 -0400, Robert Haas wrote:
>> Based on reading through this discussion, it appears that LATERAL is
>> mostly a bit of syntactic sugar that requests that the parser allow
>> you to reference tables at the same query
On Sun, 2009-09-06 at 23:59 -0400, Robert Haas wrote:
> Based on reading through this discussion, it appears that LATERAL is
> mostly a bit of syntactic sugar that requests that the parser allow
> you to reference tables at the same query level. Assuming that the
> necessary executor support were
On Sun, Sep 06, 2009 at 11:59:22PM -0400, Robert Haas wrote:
> I've attempted to search the archives for references to the SQL
> LATERAL feature, which AIUI is fairly-frequently requested.
> [snip]
> Has anyone poked at this at all?
I believe Andrew (RhodiumToad) Gierth is taking a look at
impleme
I've attempted to search the archives for references to the SQL
LATERAL feature, which AIUI is fairly-frequently requested. Most of
the discussion that I've found harks back to 2003, and that seems to
discuss more the need for eventual support of the feature than exactly
what it is or how to imple
69 matches
Mail list logo
