From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Lewis <mlewis@entrata.com>
cc: Burhan Akbulut <burhan.akbulut@cooksoft.com.tr>,
        postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Hstore index for full text search
In-reply-to: 
 <CAHOFxGoZo8U8MvQDwjDu31ByqdbqW97m8Ud_BQe-E17wzL5qZw@mail.gmail.com>
References: 
 <CACfxLrsWJh-nT1qbN3THbPD5igBWDHpj6acn77bD2p=netyq5A@mail.gmail.com>
 <CAHOFxGoZo8U8MvQDwjDu31ByqdbqW97m8Ud_BQe-E17wzL5qZw@mail.gmail.com>
Comments: In-reply-to Michael Lewis <mlewis@entrata.com>
	message dated "Tue, 11 Aug 2020 15:55:16 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <69327.1597185992.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 11 Aug 2020 18:46:32 -0400
Message-ID: <69328.1597185992@sss.pgh.pa.us>
Precedence: bulk

Michael Lewis <mlewis@entrata.com> writes:
> Hash Cond: (o.courier_id =3D 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%'::tex=
t)
> OR ((city.name)::text ~~* '%1654323%'::text) OR ((co.name)::text ~~*
> '%1654323%'::text) OR ((o.tr_code)::text ~~* '%1654323%'::text) OR ((o.o=
ps
> ? '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 wil=
l
> be unlikely that the planner will do anything with the index you are try=
ing
> 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