From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Rob Nikander <rob(dot)nikander(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: querying both text and non-text properties |
Date: | 2018-12-04 21:59:45 |
Message-ID: | 917ede32e461b787966bf1825724edc0bd2787ca.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Rob Nikander wrote:
> I’ve got an application where I’d like to search a collection of objects
> based on various properties, some text and others non-text (bools, enums,
> ints, etc). I’ve used full text search before, following the PG docs to
> set up a index on a ts_vector. And of course I’ve used normal indexes
> before for accelerating basic queries that use non-text columns.
>
> Any recommendations on combining the two? For example, if I search for
> objects where the description text matches [some ts_query] and the
> color = red, I can imagine putting the color property into the text
> index somehow - maybe with tokens like: ’color_red’, ‘color_blue’,
> but for something like an integer … ?
You have two options:
A combined index:
CREATE EXTENSION btree_gin;
CREATE INDEX ON fulltext USING gin (to_tsvector('english', doc), color);
That is the perfect match for a query with
WHERE color = 'red' AND to_tsvector('german', doc) @@ to_tsquery('english', 'word');
But you can also create two indexes:
CREATE INDEX ON fulltext USING gin (to_tsvector('english', doc));
CREATE INDEX ON fulltext (color);
Then you don't need the extension, and PostgreSQL can still use them for the search,
either only one of them if the condition is selective enough, or a "BitmapAnd" of both.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Ramsey | 2018-12-04 22:25:03 | Re: Geographic coordinate values format conversion to DD (Decimal Degrees) format |
Previous Message | Rob Sargent | 2018-12-04 21:51:31 | Re: simple division |