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 1pEHLY-0001Ds-41 for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 22:07:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEHLW-0004xF-VI for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 22:07:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEHLW-0004x6-LK for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 22:07:22 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEHLU-0000rz-JL for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 22:07:21 +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 307M7G9K3484389; Sat, 7 Jan 2023 17:07:16 -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> <3481346.1673127177@sss.pgh.pa.us> Comments: In-reply-to Marc Millas message dated "Sat, 07 Jan 2023 22:47:24 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3484387.1673129236.1@sss.pgh.pa.us> Date: Sat, 07 Jan 2023 17:07:16 -0500 Message-ID: <3484388.1673129236@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Marc Millas writes: > I read your answer, Tom, but I cannot connect it to my measurements: why > adding the index did slow the request twice ?? Are you referring to >>> if I do create a gist index on geometry column of the big table, >>> both syntax takes 21 seconds. ? That result is pretty much what I'd expect. If the planner has to form a nestloop-with-inner-indexscan between a small table and a big one, it's pretty much always going to prefer to put the small table on the outside of the loop if it can. The cost of such a loop is going to be more or less number of outer rows times the log of the number of inner rows (assuming at great risk of oversimplification that an index probe into a table of size N requires about O(log N) work), and it's not hard to see that S * log(B) is less than B * log(S). You seem to have lucked into a case where the other way comes out faster despite that, which perhaps can be explained by buffering effects, but it's not something to bet on across-the-board. (If you see this holding consistently, maybe it'd be advisable to reduce the planner's effective_cache_size parameter to something closer to shared_buffers, as it seems to indicate that fetches from kernel space are pretty expensive on your platform.) regards, tom lane