Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qz0MJ-00CgL7-IK for pgsql-general@arkaria.postgresql.org; Fri, 03 Nov 2023 20:01:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qz0MI-0033CZ-8C for pgsql-general@arkaria.postgresql.org; Fri, 03 Nov 2023 20:01:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qz0MH-0033CR-Tm for pgsql-general@lists.postgresql.org; Fri, 03 Nov 2023 20:01:33 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qz0MF-003oal-Fo for pgsql-general@lists.postgresql.org; Fri, 03 Nov 2023 20:01:32 +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 3A3K1Uqd3334147; Fri, 3 Nov 2023 16:01:30 -0400 From: Tom Lane To: David Gauthier cc: pgsql-general@lists.postgresql.org Subject: Re: Indexing fragments of a column's value ? In-reply-to: References: Comments: In-reply-to David Gauthier message dated "Fri, 03 Nov 2023 15:33:51 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3334145.1699041690.1@sss.pgh.pa.us> Date: Fri, 03 Nov 2023 16:01:30 -0400 Message-ID: <3334146.1699041690@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk David Gauthier writes: > I'm asking about the possibility of indexing portions of a column's value > where the column has a static field format. GIN indexes are meant for exactly that. You might have to write your own opclass to break up the input values in the way you want though. A less difficult answer would be to write a function that breaks up the input into (say) an array of text and then use the existing GIN array support. But you'd pay for that by needing to write more complicated queries to use the index. regards, tom lane