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 given query is, I think, in
> principle equivalent to
>  SELECT ...
>   WHERE (f1, f2::float) IN
> and if you ask unmodified HEAD to plan that you get
>  Hash Join  (cost=41.55..84.83 rows=442 width=16)
>    Hash Cond: ((upper.f1 = subselect_tbl.f2) AND ((upper.f2)::double 
> precision = subselect_tbl.f3))
>    ->  Seq Scan on subselect_tbl upper  (cost=0.00..27.70 rows=1770 width=16)
>    ->  Hash  (cost=38.55..38.55 rows=200 width=12)
>          ->  HashAggregate  (cost=36.55..38.55 rows=200 width=12)
>                ->  Seq Scan on subselect_tbl  (cost=0.00..27.70 rows=1770 
> width=12)

Before I opened your mail, I also recalled the technique that I noticed
in the planner code, to evaluate SEMI JOIN as INNER JOIN with the RHS
uniquified, so also thought it could be about the uniquification.

> which is the same thing at the visible level of detail ... but this
> version computes the correct result.  The cost of the HashAggregate is
> estimated higher, though, which suggests that maybe it's distinct'ing on
> two columns where the bogus plan only does one.

debug_print_plan output contains
:grpColIdx 2
in the AGG node. I think this corresponds to the join condition, which
IMO should be
        (upper.f1 = subselect_tbl.f2)
while the other condition was not in the list of join clauses and
therefore ignored for the uniquification's purpose.

And gdb tells me that create_unique_path() never gets more than 1
clause. I can't tell whether it should do just for this special purpose.

> Not sure about where Antonin's patch is going off the rails.  I suspect
> it's too simple somehow, but it's also possible that it's OK and the
> real issue is some previously undetected bug in LATERAL processing.

So far I have no idea how to achieve such conditions without this patch.
Thanks for your comments.

// Antonin Houska (Tony)

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to