From: | Tony Shelver <tshelver(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: querying both text and non-text properties |
Date: | 2018-12-07 06:36:27 |
Message-ID: | CAG0dhZBfLdH-EvbyOKL2hzRdiC3SjXc6dQ2O-sW9sCzyNWo_JA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)nikander(at)gmail(dot)com> wrote:
>
>
> > On Dec 4, 2018, at 4:59 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | rob stone | 2018-12-07 07:47:07 | Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI |
Previous Message | Adrian Klaver | 2018-12-07 05:29:56 | Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI |