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 1phjkG-0000OG-RF for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 04:18:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phjkE-0007S3-Uk for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 04:18:38 +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 1phjkE-0007Ru-Jd for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 04:18:38 +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 1phjk7-0004My-Rb for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 04:18:38 +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 32U4ISl14136182; Thu, 30 Mar 2023 00:18:28 -0400 From: Tom Lane To: Julien Rouhaud cc: Andrey Klochkov , Francisco Olarte , Vladimir Sitnikov , "pgsql-generallists.postgresql.org" Subject: Re: Do BRIN indexes support MIN/MAX? In-reply-to: References: Comments: In-reply-to Julien Rouhaud message dated "Thu, 30 Mar 2023 12:02:44 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <4136180.1680149908.1@sss.pgh.pa.us> Date: Thu, 30 Mar 2023 00:18:28 -0400 Message-ID: <4136181.1680149908@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Julien Rouhaud writes: > brin indexes don't work the way you would hope for. the stored min/max > values per range guarantees that all values in the underlying relation > pages are contained in that range, but it doesn't mean that those min/max > values are still present in the table, so you can't deduce in which range > the current min or max value is from there. Yeah. You could for example (when looking for a MAX) skip scanning block ranges whose indexed MAX is less than the indexed MIN of some other block range. But unless the column you are interested in is pretty well correlated with physical storage order, that seems unlikely to buy much. [ ... thinks ... ] Actually, I'm not sure that even that optimization works without hackery. It requires that you know that the "other block range" is not empty according to your snapshot. I guess you could work by scanning block ranges in descending indexed-MIN order, and skipping ranges only once you have a candidate MAX in hand. But that sounds complicated and very prone to nonsequential table access, so I'm not sure how much of a win it is. If you need fast min/max access, you probably need to bite the bullet and maintain a btree index. regards, tom lane