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 1k5djZ-0000g9-Ah for pgsql-performance@arkaria.postgresql.org; Tue, 11 Aug 2020 23:31:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k5djV-0004kl-N4 for pgsql-performance@arkaria.postgresql.org; Tue, 11 Aug 2020 23:31:05 +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 1k5djU-0004kd-TX for pgsql-performance@lists.postgresql.org; Tue, 11 Aug 2020 23:31:04 +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 1k5djS-0003cq-D7 for pgsql-performance@postgresql.org; Tue, 11 Aug 2020 23:31:03 +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 07BNUwk2071101; Tue, 11 Aug 2020 19:30:58 -0400 From: Tom Lane To: Michael Lewis cc: Burhan Akbulut , postgres performance list Subject: Re: Hstore index for full text search In-reply-to: References: <69328.1597185992@sss.pgh.pa.us> Comments: In-reply-to Michael Lewis message dated "Tue, 11 Aug 2020 16:57:40 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <71099.1597188658.1@sss.pgh.pa.us> Date: Tue, 11 Aug 2020 19:30:58 -0400 Message-ID: <71100.1597188658@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Michael Lewis writes: > On Tue, Aug 11, 2020 at 4:46 PM Tom Lane wrote: >> Another point is that you will only >> get an indexscan if *every* OR'd clause matches some index. The example >> query looks sufficiently unstructured that that might be hard to ensure. > Does this still apply when the where clauses are on several tables and not > just one? Yeah. In that case there's no hope of an indexscan at all, since for all the planner knows, the query might match some table rows that don't meet any of the conditions mentioned for that table's columns. If you can write WHERE (condition-on-t1.a OR condition-on-t1.b OR ...) AND (condition-on-t2.x OR condition-on-t2.y OR ...) then you've got a chance of making the OR'd conditions into index qualifications on t1 or t2 respectively. But if it's WHERE condition-on-t1.a OR condition-on-t1.b OR ... OR condition-on-t2.x OR condition-on-t2.y OR ... then you're in for full-table scans. (This is another thing that was bothering me about the data design, though I failed to think it through clearly enough to state before.) regards, tom lane