Re: querying both text and non-text properties
| От | Tony Shelver |
|---|---|
| Тема | Re: querying both text and non-text properties |
| Дата | |
| Msg-id | CAG0dhZBfLdH-EvbyOKL2hzRdiC3SjXc6dQ2O-sW9sCzyNWo_JA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: querying both text and non-text properties (Rob Nikander <rob.nikander@gmail.com>) |
| Список | pgsql-general |
I would suggest doing testing out btree_gin with a non-insignificant amount of data before going ahead with it.
I did a test case last month, and the size of the generated index was _much_ bigger than the base table.
The case involved a compound key if 1 int column and 1 timestamp range column.
On Wed, 5 Dec 2018 at 16:02, Rob Nikander <rob.nikander@gmail.com> wrote:
> On Dec 4, 2018, at 4:59 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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.
Thanks! I will try both these methods and compare the performance.
Rob
В списке pgsql-general по дате отправления: