Horton

Re: convert EXSITS to inner join gotcha and bug

Teodor Sigaev <teodor@sigaev.ru>

View thread View on postgr.es
From: Teodor Sigaev <teodor@sigaev.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <david.rowley@2ndquadrant.com>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>, Pgsql Hackers <pgsql-hackers@postgresql.org>
Date: 2017-04-29T17:35:05Z
Lists: pgsql-hackers 
> Really, the way to fix Teodor's complaint is to recognize that the
> semijoin inner rel is effectively unique against the whole outer rel,
> and then strength-reduce the semijoin to a plain join.  The infrastructure
> we built for unique joins is capable of proving that, we just weren't
> applying it in the right way.
Perfect, it works. Thank you! Second patch reduces time of full query (my 
example was just a small part) from 20 minutes to 20 seconds.

> I'm kind of strongly tempted to apply the second patch; but it would
> be fair to complain that reduce_unique_semijoins() is new development
> and should wait for v11.  Opinions?

Obviously, I'm voting for second patch applied to version 10.

-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Commits

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

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