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 18:46:32 -0400
Message-ID: <69328.1597185992@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHOFxGoZo8U8MvQDwjDu31ByqdbqW97m8Ud_BQe-E17wzL5qZw@mail.gmail.com>
References: <CACfxLrsWJh-nT1qbN3THbPD5igBWDHpj6acn77bD2p=netyq5A@mail.gmail.com>
<CAHOFxGoZo8U8MvQDwjDu31ByqdbqW97m8Ud_BQe-E17wzL5qZw@mail.gmail.com>
Michael Lewis <mlewis@entrata.com> writes:
> Hash Cond: (o.courier_id = cc.id)
> Filter: (((o.tracker_code)::text ~~* '%1654323%'::text) OR
> ((table_cus.name)::text
> ~~* '%1654323%'::text) OR ((au.username)::text ~~ '%1654323%'::text) OR
> ((o.source)::text ~~* '%1654323%'::text) OR ((o.ops -> 'shop'::text) ~~*
> '%1654323%'::text) OR ((o.ops -> 'camp_code'::text) ~~* '%1654323%'::text)
> OR ((city.name)::text ~~* '%1654323%'::text) OR ((co.name)::text ~~*
> '%1654323%'::text) OR ((o.tr_code)::text ~~* '%1654323%'::text) OR ((o.ops
> ? 'shipping_company'::text) AND ((o.ops -> 'shipping_company'::text) ~~*
> '%1654323%'::text)) OR ((cc.name)::text ~~* '%1654323%'::text))
> All those OR conditions on different tables and fields seems like it will
> be unlikely that the planner will do anything with the index you are trying
> to create (for this query).
A GIN index on an hstore column only provides the ability to search for
exact matches to hstore key strings. There are a few bells and whistles,
like the ability to AND or OR such conditions. But basically it's just an
exact-match engine, and it doesn't index the hstore's data values at all
(which is why the implementors weren't too concerned about having a length
limit on the index entries). There is 0 chance of this index type being
useful for what the OP wants to do.
Given these examples, I'd think about setting up a collection of pg_trgm
indexes on the specific hstore keys you care about, ie something like
CREATE INDEX ON mytable USING GIST ((ops -> 'camp_code') gist_trgm_ops);
CREATE INDEX ON mytable USING GIST ((ops -> 'shipping_company') gist_trgm_ops);
...
which'd allow indexing queries like
... WHERE (ops -> 'camp_code') LIKE '%1654323%'
OR (ops -> 'shipping_company') LIKE '%1654323%'
I'm not sure how far this will get you, though; if there's a whole lot
of different keys of interest, maintaining a separate index for each
one is probably too much overhead. 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.
I kind of wonder whether this data design is actually a good idea.
It doesn't seem to match your querying style terribly well.
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: <69328.1597185992@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