On 12/12/2013 04:36 PM, Tom Lane wrote:
BTW, on further thought, I'm afraid this is a bigger can of worms than
it appears. The remarks above presume that the subquery is simple enough
to be pulled up, which is the case in this example. It might not be too
hard to make that case work. But
Tom Lane t...@sss.pgh.pa.us writes:
Yeah, I found myself wishing for an EXPLAIN option that would show
that.
It's not hard to do ... how about the attached?
+1
I chose to print grouping keys for both Agg and Group nodes, and to
show them unconditionally. There's some case maybe for only
Tom Lane t...@sss.pgh.pa.us wrote:
Hm, that means there's only one grouping column (and it's the second
tlist entry of the child plan node). So that seems conclusive that
the unique-ification is being done wrong.
Further confirmation using the EXPLAIN patch with Antonin's v2
patch against
Kevin Grittner kgri...@ymail.com writes:
Further confirmation using the EXPLAIN patch with Antonin's v2
patch against the table before any EXPLAIN or ANALYZE:
Hash Join (cost=37.12..80.40 rows=442 width=12)
Hash Cond: (((upper.f2)::double precision = lower.f3) AND (upper.f1 =
I wrote:
That's about what I thought: it's unique-ifying according to the original
semijoin qual, without realizing that the pulled-up clause from the lower
WHERE would need to be treated as part of the semijoin qual. This isn't
a bug in the existing code, because the case can never arise,
Kevin Grittner kgri...@ymail.com wrote:
I applied it to master and ran the regression tests, and one of
the subselect tests failed.
This query:
SELECT '' AS six, f1 AS Correlated Field, f3 AS Second
Field
FROM SUBSELECT_TBL upper
WHERE f1 IN
(SELECT f2 FROM SUBSELECT_TBL WHERE
Kevin Grittner kgri...@ymail.com writes:
Kevin Grittner kgri...@ymail.com wrote:
I applied it to master and ran the regression tests, and one of
the subselect tests failed.
This query:
SELECT '' AS six, f1 AS Correlated Field, f3 AS Second
Field
FROM SUBSELECT_TBL upper
WHERE f1
Tom Lane t...@sss.pgh.pa.us wrote:
FWIW, that plan isn't obviously wrong; if it is broken, most
likely the reason is that the HashAggregate is incorrectly
unique-ifying the lower table. (Unfortunately, EXPLAIN doesn't
show enough about the HashAgg to know what it's doing exactly.)
Yeah, I
Kevin Grittner kgri...@ymail.com writes:
Tom Lane t...@sss.pgh.pa.us wrote:
FWIW, that plan isn't obviously wrong; if it is broken, most
likely the reason is that the HashAggregate is incorrectly
unique-ifying the lower table. (Unfortunately, EXPLAIN doesn't
show enough about the HashAgg to
On 12/11/2013 10:15 PM, Tom Lane wrote:
FWIW, that plan isn't obviously wrong; if it is broken, most likely the
reason is that the HashAggregate is incorrectly unique-ifying the lower
table. (Unfortunately, EXPLAIN doesn't show enough about the HashAgg
to know what it's doing exactly.) The
Antonin Houska antonin.hou...@gmail.com writes:
debug_print_plan output contains
:grpColIdx 2
in the AGG node.
Hm, that means there's only one grouping column (and it's the second
tlist entry of the child plan node). So that seems conclusive that
the unique-ification is being done wrong.
On 12/06/2013 03:33 PM, Kevin Grittner wrote:
Antonin Houska antonin.hou...@gmail.com wrote:
SELECT *
FROMtab1 a
LEFT JOIN
tab2 b
ON a.i = ANY (
SELECT k
FROMtab3 c
WHEREk = a.i);
This query works with k in any or all tables, but
Antonin Houska antonin.hou...@gmail.com wrote:
I used the DDLs attached (tables.ddl) for this query too, not
only for the queries in quaries.sql. Yes, if I had mentioned it
and/or qualified the 'k' column reference, it wouldn't have
broken anything.
Apologies; I missed the attachments. It
Antonin Houska antonin.hou...@gmail.com wrote:
SELECT *
FROM tab1 a
LEFT JOIN
tab2 b
ON a.i = ANY (
SELECT k
FROM tab3 c
WHERE k = a.i);
This query works with k in any or all tables, but the semantics
certainly vary depending on where k
Kevin Grittner kgri...@ymail.com wrote:
test=# SELECT *
FROM tab1 a
LEFT JOIN
tab2 b
ON a.i = ANY (
SELECT k
FROM tab3 c
WHERE k = a.i);
i | j
---+---
1 | 4
1 | 5
1 | 6
2 |
3 | 4
3 | 5
3 | 6
(7 rows)
SELECT *
FROM
On 10/31/2013 09:37 PM, Antonin Houska wrote:
On 10/31/2013 03:46 PM, Antonin Houska wrote:
I'm not sure if it's legal for the WHERE clause to reference LHS of the
original outer join (a.j). Some more restriction may be needed. I need
to think about it a bit more.
For a subquery or sublink
On 10/31/2013 03:46 PM, Antonin Houska wrote:
Can the change be as simple as this or do I neglect anything?
Well, the example of outer join is wrong. Instead I think query
SELECT *
FROMtab1 a
LEFT JOIN
tab1 b
ON b.i = ANY (
SELECT tab2.k
17 matches
Mail list logo
