From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Lewis <mlewis@entrata.com>
Cc: Burhan Akbulut <burhan.akbulut@cooksoft.com.tr>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Hstore index for full text search
Date: Tue, 11 Aug 2020 19:30:58 -0400
Message-ID: <71100.1597188658@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHOFxGp-zFFyF+3-q30Pb1gSAdE5+3eeedUqkdetRX0jt8yPfQ@mail.gmail.com>
References: <CACfxLrsWJh-nT1qbN3THbPD5igBWDHpj6acn77bD2p=netyq5A@mail.gmail.com>
<CAHOFxGoZo8U8MvQDwjDu31ByqdbqW97m8Ud_BQe-E17wzL5qZw@mail.gmail.com>
<69328.1597185992@sss.pgh.pa.us>
<CAHOFxGp-zFFyF+3-q30Pb1gSAdE5+3eeedUqkdetRX0jt8yPfQ@mail.gmail.com>
Michael Lewis <mlewis@entrata.com> writes:
> On Tue, Aug 11, 2020 at 4:46 PM Tom Lane <tgl@sss.pgh.pa.us> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mlewis@entrata.com, burhan.akbulut@cooksoft.com.tr
Subject: Re: Hstore index for full text search
In-Reply-To: <71100.1597188658@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox