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 move
Antonin Houska antonin.hou...@gmail.com 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
I have couple of questions.
On Wed, Jun 26, 2013 at 1:30 AM, Tom Lane t...@sss.pgh.pa.us 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
Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes:
On Wed, Jun 26, 2013 at 1:30 AM, Tom Lane t...@sss.pgh.pa.us 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
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
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
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:
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
Antonin Houska antonin.hou...@gmail.com 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
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,
(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
Antonin Houska antonin.hou...@gmail.com 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;
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 ado:
* 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,
but I
Stephen Frost sfr...@snowman.net 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
* 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 ...)
I
* 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)
Stephen Frost sfr...@snowman.net 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),
David Fetter da...@fetter.org 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
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 table reference
6)
a) If TR is contained in a from clause FC with no intervening query
expression, then the
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
Pavel Stehule pavel.steh...@gmail.com 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)
2012/12/15 Tom Lane t...@sss.pgh.pa.us:
Pavel Stehule pavel.steh...@gmail.com writes:
Is this behave expected?
-- unexpected
postgres=# select * from generate_series(1,3) g(v), LATERAL (SELECT
random()) x;
;
vrandom
---+--
1 0.63025646051392
2
Robert Haas robertmh...@gmail.com writes:
On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us 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
On Sat, Mar 10, 2012 at 4:16 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Um ... how do you get the subquery result rows to join to only the
correct rows of the other tables? This looks like
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
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
Antonin Houska antonin.hou...@gmail.com 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 c1
may become
SELECT *
FROM a, b, subquery as c, d
WHERE a.i=b.j and b.j = d.k and c1
where subquery is
SELECT func(a.i, b.j)
FROM a,b
WHERE
On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Antonin Houska antonin.hou...@gmail.com 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 c1
may become
SELECT *
FROM a, b, subquery as c, d
WHERE a.i=b.j and b.j = d.k
Robert Haas wrote:
On Sat, Dec 19, 2009 at 11:01 PM, Robert Haas robertmh...@gmail.com wrote:
On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I believe the
2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk:
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
On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada umi.tan...@gmail.com wrote:
2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk:
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
2009/12/20 Robert Haas robertmh...@gmail.com:
On Sat, Dec 19, 2009 at 12:49 PM, Hitoshi Harada umi.tan...@gmail.com wrote:
2009/10/20 Andrew Gierth and...@tao11.riddles.org.uk:
Right now, the only way pg can plan this is to do a hashjoin or
mergejoin of the _entire content of big1 and big2_
On Thu, Dec 17, 2009 at 10:13 PM, Robert Haas robertmh...@gmail.com 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
Robert Haas robertmh...@gmail.com 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
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
Robert Haas robertmh...@gmail.com writes:
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us 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,
On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I believe the correct approach is probably to treat values that need to
be propagated into the inner side as
On Sat, Dec 19, 2009 at 11:01 PM, Robert Haas robertmh...@gmail.com wrote:
On Sat, Dec 19, 2009 at 4:46 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sat, Dec 19, 2009 at 3:01 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I believe the correct approach is
Robert Haas robertmh...@gmail.com 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
On Sun, Oct 18, 2009 at 2:57 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
On Sun, Oct 18, 2009 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
Greg Stark gsst...@mit.edu writes:
nested loop
index scan expecting 1 record
merge join
index scan on col1,col2 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
Greg == Greg Stark gsst...@mit.edu 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
On Sat, Oct 17, 2009 at 10:09 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
Robert Haas robertmh...@gmail.com 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
On Sun, Oct 18, 2009 at 11:30 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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.
Robert Haas robertmh...@gmail.com 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
On Sun, Oct 18, 2009 at 3:57 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
On Tue, Sep 22, 2009 at 11:21 PM, Tom Lane t...@sss.pgh.pa.us 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,
Robert Haas robertmh...@gmail.com 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
Robert Haas robertmh...@gmail.com 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
On Wed, Sep 9, 2009 at 11:25 PM, Andrew Gierth
and...@tao11.riddles.org.uk 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
Robert Haas robertmh...@gmail.com 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
Robert == Robert Haas robertmh...@gmail.com 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
On mån, 2009-09-07 at 19:06 -0400, Robert Haas wrote:
On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentrautpete...@gmx.net 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]:
David == David Fetter da...@fetter.org 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
On Tue, Sep 8, 2009 at 6:29 PM, Andrew
Gierthand...@tao11.riddles.org.uk wrote:
David == David Fetter da...@fetter.org 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
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 Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentrautpete...@gmx.net 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
Robert Haas robertmh...@gmail.com writes:
On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentrautpete...@gmx.net 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
On Mon, Sep 7, 2009 at 7:47 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentrautpete...@gmx.net wrote:
You could argue that the parser could infer the references and the
resultant join ordering restrictions
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
Robert,
* Robert Haas (robertmh...@gmail.com) wrote:
On Mon, Sep 7, 2009 at 7:47 PM, Tom Lanet...@sss.pgh.pa.us 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
On Mon, Sep 7, 2009 at 9:12 PM, Stephen Frostsfr...@snowman.net wrote:
Robert,
* Robert Haas (robertmh...@gmail.com) wrote:
On Mon, Sep 7, 2009 at 7:47 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Because as often as not, they're mistakes. Please don't think
you're smarter than the spec here.
* 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 10:08 PM, Stephen Frostsfr...@snowman.net 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
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
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
69 matches
Mail list logo
