From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: jesper@krogh.cc
Cc: pgsql-performance@postgresql.org
Cc: oleg@sai.msu.su
Cc: teodor@sigaev.ru
Subject: Re: Queryplan within FTS/GIN index -search.
Date: Tue, 03 Nov 2009 10:49:31 -0600
Message-ID: <4AF00ABB020000250002C1B3@gw.wicourts.gov> (raw)
In-Reply-To: <4730.1257262544@sss.pgh.pa.us>
References: <4AEF0369020000250002C183@gw.wicourts.gov>
<4AEF0369020000250002C183@gw.wicourts.gov>
<11587.1257207482@sss.pgh.pa.us>
<4AEFF056020000250002C195@gw.wicourts.gov>
<4AEFF056020000250002C195@gw.wicourts.gov>
<4730.1257262544@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The answer to that clearly is to not index common terms
My understanding is that we don't currently get statistics on how
common the terms in a tsvector column are until we ANALYZE the *index*
created from it. Seems like sort of a Catch 22. Also, if we exclude
words which are in the tsvector from the index on the tsvector, we
need to know what words were excluded so we know not to search on them
as well as forcing the recheck of the full tsquery (unless this always
happens already?).
> It may well be that Jesper's identified a place where the GIN code
> could be improved
My naive assumption has been that it would be possible to get an
improvement without touching the index logic, by changing this part of
the query plan:
Index Cond: (ftsbody_body_fts @@ to_tsquery
('TERM1 & TERM2 & TERM3 & TERM4 & TERM5'::text))
to something like this:
Index Cond: (ftsbody_body_fts @@ to_tsquery
('TERM1'::text))
and count on this doing the rest:
Recheck Cond: (ftsbody_body_fts @@ to_tsquery
('TERM1 & TERM2 & TERM3 & TERM4 & TERM5'::text))
I'm wondering if anyone has ever confirmed that probing for the more
frequent term through the index is *ever* a win, versus using the
index for the most common of the top level AND conditions and doing
the rest on recheck. That seems like a dangerous assumption from
which to start.
> But the particular example shown here doesn't make a very good case
> for that, because it's hard to tell how much of a penalty would be
> taken in more realistic examples.
Fair enough. We're in the early stages of moving to tsearch2 and I
haven't run across this yet in practice. If I do, I'll follow up.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us, jesper@krogh.cc, oleg@sai.msu.su, teodor@sigaev.ru
Subject: Re: Queryplan within FTS/GIN index -search.
In-Reply-To: <4AF00ABB020000250002C1B3@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox