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 1pEGoO-00086d-AS for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 21:33:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEGoM-0001h7-GA for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 21:33:06 +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 1pEGoM-0001gx-5N for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 21:33:06 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEGoJ-0005L8-Dt for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 21:33:05 +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 307LWvuu3481347; Sat, 7 Jan 2023 16:32:57 -0500 From: Tom Lane To: Marc Millas cc: Erik Wienhold , "pgsql-general@lists.postgresql.org" Subject: Re: impact join syntax ?? and gist index ?? In-reply-to: References: <832577793.126542.1673124009766@office.mailbox.org> Comments: In-reply-to Marc Millas message dated "Sat, 07 Jan 2023 21:57:43 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3481345.1673127177.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sat, 07 Jan 2023 16:32:57 -0500 Message-ID: <3481346.1673127177@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Marc Millas writes: > 2023=3D# explain (analyze, buffers) select A.x, count(B.x) from A left j= oin B > on st_within(B.geom, A.geom) group by A.x; So the problem with this is that the only decently-performant way to do the join is like > -> Nested Loop (cost=3D0.13..6275745.36 rows=3D49= 71999 width=3D16) (actual time=3D0.505..11781.817 rows=3D210651 loops=3D2) > -> Parallel Seq Scan on B (cost=3D0.00..184= 54.99 rows=3D248699 width=3D40) (actual time=3D0.005..22.859 rows=3D211395= loops=3D2) > -> Index Scan using A_geom_idx on A (cost=3D= 0.13..25.15 rows=3D1 width=3D17752) (actual time=3D0.054..0.055 rows=3D1 l= oops=3D422789) > Index Cond: (geom ~ B.geom) > Filter: st_within(B.geom, geom) (Ignore the parallelism, it's not very relevant here.) There's no chance for merge or hash join because those require simple equality join conditions. The only way to avoid a stupid compare-every-row-of-A-to-every-row-of-B nestloop is to use a parameterized inner indexscan, as this plan does. But that only works if the join is inner or has the indexed table on the nullable side. We have no support for nestloop right join, which is what would be needed to make things run fast with no index on B. regards, tom lane