Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qDXaX-0002Zb-FU for pgsql-general@arkaria.postgresql.org; Sun, 25 Jun 2023 21:48:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qDXaU-0000If-Kn for pgsql-general@arkaria.postgresql.org; Sun, 25 Jun 2023 21:48:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qDXaU-0000IV-9j for pgsql-general@lists.postgresql.org; Sun, 25 Jun 2023 21:48:02 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qDXaR-000Se9-CU for pgsql-general@lists.postgresql.org; Sun, 25 Jun 2023 21:48:01 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 35PLlt5c587382; Sun, 25 Jun 2023 17:47:55 -0400 From: Tom Lane To: David Rowley cc: Marc Millas , "pgsql-general@lists.postgresql.org" Subject: Re: bug or lacking doc hint In-reply-to: References: Comments: In-reply-to David Rowley message dated "Mon, 26 Jun 2023 07:35:14 +1200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <587380.1687729675.1@sss.pgh.pa.us> Date: Sun, 25 Jun 2023 17:47:55 -0400 Message-ID: <587381.1687729675@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk David Rowley writes: > The problem is that out of the 3 methods PostgreSQL uses to join > tables, only 1 of them supports join conditions with an OR clause. > Merge Join cannot do this because results can only be ordered one way > at a time. Hash Join technically could do this, but it would require > that it built multiple hash tables. Currently, it only builds one > table. That leaves Nested Loop as the join method to implement joins > with OR clauses. Unfortunately, nested loops are quadratic and the > join condition must be evaluated once per each cartesian product row. We can do better than that if the OR'd conditions are each amenable to an index scan on one of the tables: then it can be a nestloop with a bitmap-OR'd inner index scan. I thought the upthread advice to convert the substr() condition into something that could be indexed was on-point. > Tom Lane did start some work [1] to allow the planner to convert some > queries to use UNION instead of evaluating OR clauses, but, if I > remember correctly, it didn't handle ORs in join conditions, though > perhaps having it do that would be a natural phase 2. I don't recall > why the work stopped. As I recall, I was having difficulty convincing myself that de-duplication of results (for cases where the same row satisfies more than one of the OR'd conditions) would work correctly. You can't just blindly make it a UNION because that might remove identical rows that *should* appear more than once in the result. regards, tom lane