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 1nWTEv-00057x-AI for pgsql-hackers@arkaria.postgresql.org; Tue, 22 Mar 2022 01:23:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nWTEu-000246-5a for pgsql-hackers@arkaria.postgresql.org; Tue, 22 Mar 2022 01:23:12 +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 1nWTEt-00023t-Sz for pgsql-hackers@lists.postgresql.org; Tue, 22 Mar 2022 01:23:11 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nWTEr-0000og-JX for pgsql-hackers@lists.postgresql.org; Tue, 22 Mar 2022 01:23:10 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id AA61D5C01A7; Mon, 21 Mar 2022 21:23:08 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Mon, 21 Mar 2022 21:23:08 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm1; bh=AjCqmyCO4FMoWRT5qfwHJjpQGY2wvBfa4SO75q ioxaQ=; b=sy+aanzXAhG6mvMFP1jO9+FVd2cyushMqCVb05vveM+8m02+MZEK4a YOMjHEndEX6gmGaXQsHSH/3EmFWVv2CjUWCSBCmc0HiaHjmh0Fxbwf4QHPVjbyG9 lYWMy+oR6yvZhbhOxRUfa+pq+acmBiJP9dsn5dWD1kFw86vxPGjltdFKGJl2BDqE MYc/ushklbSbAhFqy1/2Ed/UnzeuDHEgH4MYhZIqjeQJUqTC4HL5/M3MJhOZwf3n PIdj1Nmg04Mb+uFyAhFKF1Aflsts5wfB7CXgL5IUoi/Uhwr4HQSvuer6eqtAT9r9 tm73dYgYq/qLIuSehEufqoaZWCu96umA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; bh=AjCqmyCO4FMoWRT5q fwHJjpQGY2wvBfa4SO75qioxaQ=; b=FYPiaeDEkSx44N33HHSMPIJtXvHLm2qUz RE9SljMVC+dfnH6lmJX8NTC1LPPGA3Xp+z8aaEtw5qTKVLlqxBy+iKSw5GPtTGJo zRNM1h7dgTxzixlvKBE1SnIY9ygKTwRQ34h4kzYFAH6T+gGbynIbaLdoLjiA7vZH uSQyHmrYb8ODNU7fToNI+xShACZBKwhr2RPPqlZk8sAHEiCHVhE9o+ACEXikFzP+ IL1h1qKbdN6nUuK8WZM4mDT8ooSMLFRz+IHyRuCDqYuwAvI2v4PYAdCSWeqVpt3Q f/7kxkq1Qks+09mHqZgCvdUt31UxtDxq9pzG/uT/oWcetuG+gF22g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvvddrudeggedgfedvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffvuffkfhggtggujgfhsehttdertddttddvnecuhfhrohhmpeetnhgurhgv shcuhfhrvghunhguuceorghnughrvghssegrnhgrrhgriigvlhdruggvqeenucggtffrrg htthgvrhhnpeeikeehheefgeegteffuddttdeifeduieeftedvffekudegkedtieffleeg ffejhfenucffohhmrghinheptghpuhhtuhgsvgdrohhrghenucevlhhushhtvghrufhiii gvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrnhgurhgvshesrghnrghrrgiivghl rdguvg X-ME-Proxy: Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 21 Mar 2022 21:23:08 -0400 (EDT) Date: Mon, 21 Mar 2022 18:23:07 -0700 To: Andrey Borodin Cc: Aliaksandr Kalenik , pgsql-hackers@lists.postgresql.org Subject: Re: [PATCH] sort leaf pages by ctid for gist indexes built using sorted method Message-ID: <20220322012307.wladipqgecbwqpjv@alap3.anarazel.de> [IFrom: Andres Freund References: <264911639648165@sas1-7f179212b74f.qloud-c.yandex.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <264911639648165@sas1-7f179212b74f.qloud-c.yandex.net> From: Andres Freund List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On 2021-12-16 14:49:25 +0500, Andrey Borodin wrote: > > > With the current implementation, for GiST indexes created by doing multiple inserts, index tuples match heap tuples order, but it doesn't work that way for sorted method where index tuples on all levels are ordered using comparator provided in sortsupport (z-order for geometry type, for example). This means two tuples that are on the same heap page can be far apart from one another on an index page, and the heap page may be read twice and prefetch performance will degrade. > > > > I've created a patch intended to improve that by sorting index tuples by heap tuples TID order on leaf pages. > > Thanks you for the patch. The code looks nice and clean. The patch fails currently doesn't apply: http://cfbot.cputube.org/patch_37_3454.log > But can we have some benchmarks showing that this optimization really helps? As there hasn't been a response to this even in the last CF, I'm going to mark this entry as returned with feedback (IMO shouldn't even have been moved to this CF). Greetings, Andres Freund