Horton

Re: convert EXSITS to inner join gotcha and bug

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Teodor Sigaev <teodor@sigaev.ru>, David Rowley <david.rowley@2ndquadrant.com>, Pgsql Hackers <pgsql-hackers@postgresql.org>
Date: 2017-04-28T14:26:58Z
Lists: pgsql-hackers 
Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> I've reproduced this bug on d981074c.
> On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query
> result is OK.
> But with seqscan and hashjoin disabled, query returns 0 rows.

Ah, thanks for the clue about enable_hashjoin, because it wasn't
reproducing for me as stated.

It looks like in the case that's giving wrong answers, the mergejoin
is wrongly getting marked as "Inner Unique".  Something's a bit too
cheesy about that planner logic --- not sure what, yet.

			regards, tom lane

Commits

  1. Reduce semijoins with unique inner relations to plain inner joins.

  2. Fix mis-optimization of semijoins with more than one LHS relation.