Re: Hstore index for full text search

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Michael Lewis <mlewis@xxxxxxxxxxx> 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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux