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