pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Queryplan within FTS/GIN index -search.
8+ messages / 3 participants
[nested] [flat]
* Re: Queryplan within FTS/GIN index -search.
@ 2009-11-02 22:06 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2009-11-02 22:06 UTC (permalink / raw)
To: jesper@krogh.cc; tgl@sss.pgh.pa.us; +Cc: pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Any sane text search application is going to try to filter out
> common words as stopwords; it's only the failure to do that that's
> making this run slow.
Imagine a large table with a GIN index on a tsvector. The user wants
a particular document, and is sure four words are in it. One of them
only appears in 100 documents. The other three each appear in about
a third of the documents. Is it more sane to require the user to
wait for a table scan or to make them wade through 100 rows rather
than four?
I'd rather have the index used for the selective test, and apply the
remaining tests to the rows retrieved from the heap.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2009-11-03 00:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2009-11-03 00:18 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: jesper@krogh.cc; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Any sane text search application is going to try to filter out
>> common words as stopwords; it's only the failure to do that that's
>> making this run slow.
> I'd rather have the index used for the selective test, and apply the
> remaining tests to the rows retrieved from the heap.
Uh, that was exactly my point. Indexing common words is a waste.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-11-03 14:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 15:35 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2009-11-03 14:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jesper@krogh.cc; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Any sane text search application is going to try to filter out
>>> common words as stopwords; it's only the failure to do that that's
>>> making this run slow.
>
>> I'd rather have the index used for the selective test, and apply
>> the remaining tests to the rows retrieved from the heap.
>
> Uh, that was exactly my point. Indexing common words is a waste.
Perhaps I'm missing something. My point was that there are words
which are too common to be useful for index searches, yet uncommon
enough to usefully limit the results. These words could typically
benefit from tsearch2 style parsing and dictionaries; so declaring
them as stop words would be bad from a functional perspective, yet
searching an index for them would be bad from a performance
perspective.
One solution would be for the users to rigorously identify all of
these words, include them on one stop word list but not another,
include *two* tsvector columns in the table (with and without the
"iffy" words), index only the one with the larger stop word list, and
generate two tsquery values to search the two different columns. Best
of both worlds. Sort of. The staff time to create and maintain such
a list would obviously be costly and writing the queries would be
error-prone.
Second best would be to somehow recognize the "iffy" words and exclude
them from the index and the index search phase, but apply the check
when the row is retrieved from the heap. I really have a hard time
seeing how the conditional exclusion from the index could be
accomplished, though. Next best would be to let them fall into the
index, but exclude top level ANDed values from the index search,
applying them only to the recheck when the row is read from the heap.
The seems, at least conceptually, like it could be done.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2009-11-03 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 16:49 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 18:36 ` Re: Queryplan within FTS/GIN index -search. Jesper Krogh <jesper@krogh.cc>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2009-11-03 15:35 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: jesper@krogh.cc; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Perhaps I'm missing something. My point was that there are words
> which are too common to be useful for index searches, yet uncommon
> enough to usefully limit the results. These words could typically
> benefit from tsearch2 style parsing and dictionaries; so declaring
> them as stop words would be bad from a functional perspective, yet
> searching an index for them would be bad from a performance
> perspective.
Right, but the original complaint in this thread was that a GIN index is
slow about searching for very common terms. The answer to that clearly
is to not index common terms, rather than worry about making the case
a bit faster.
It may well be that Jesper's identified a place where the GIN code could
be improved --- it seems like having the top-level search logic be more
aware of the AND/OR structure of queries would be useful. 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.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 15:35 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-11-03 16:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 17:03 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 21:50 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 8+ messages in thread
From: Kevin Grittner @ 2009-11-03 16:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jesper@krogh.cc; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 15:35 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 16:49 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2009-11-03 17:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2009-11-03 17:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: jesper@krogh.cc; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> 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.
s/most/least/
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 15:35 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 16:49 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2009-11-03 21:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2009-11-03 21:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jesper@krogh.cc; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
I wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
We have a staging database which allowed some limited testing quickly.
While it's real production data, we haven't been gathering this type
of data long, so it's got relatively few rows; therefore, it wasn't
feasible to try any tests which would be disk-bound, so I primed the
cache for all of these, and they are all totally served from cache.
For various reasons which I'll omit unless asked, we do our text
searches through functions which take a "selection string", turn it
into a tsquery with a little extra massaging on our part, run the
query with a minimum ranking to return, and return a set of records
ordered by the ranking in descending sequence.
Under these conditions there is a slight performance gain in adding an
additional test which matches 1356 out of 1691 rows. Not surprisingly
for a fully cached query set, timings were very consistent from run to
run. While undoubtedly a little unusual in approach, this is
production software run against real-world data. I confirmed that it
is using the GIN index on the tsvector for these runs.
By the way, the tsearch2 features have been received very well so
far. One of the first reactions from most users is surprise at how
fast it is. :-) Anyway, our production results don't confirm the
issue shown with the artificial test data.
scca=> select count(*) from "DocThumbnail" where "text" is not null;
count
-------
1691
(1 row)
Time: 0.619 ms
scca=> select count(*) from (select "DocThumbnail_text_rank"('guardian
ad litem', 0.1)) x;
count
-------
41
(1 row)
Time: 19.394 ms
scca=> select count(*) from (select "DocThumbnail_text_rank"('guardian
ad litem attorney', 0.1)) x;
count
-------
4
(1 row)
Time: 16.434 ms
scca=> select count(*) from (select
"DocThumbnail_text_rank"('attorney', 0.1)) x;
count
-------
1356
(1 row)
Time: 415.056 ms
scca=> select count(*) from (select "DocThumbnail_text_rank"('guardian
ad litem party', 0.1)) x;
count
-------
2
(1 row)
Time: 16.290 ms
scca=> select count(*) from (select "DocThumbnail_text_rank"('party',
0.1)) x;
count
-------
935
(1 row)
Time: 386.941 ms
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queryplan within FTS/GIN index -search.
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 15:35 ` Re: Queryplan within FTS/GIN index -search. Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-11-03 18:36 ` Jesper Krogh <jesper@krogh.cc>
1 sibling, 0 replies; 8+ messages in thread
From: Jesper Krogh @ 2009-11-03 18:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance; oleg@sai.msu.su; teodor@sigaev.ru
Tom Lane wrote:
> It may well be that Jesper's identified a place where the GIN code could
> be improved --- it seems like having the top-level search logic be more
> aware of the AND/OR structure of queries would be useful. 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.
With a term sitting in:
80% of the docs the penalty is: x23
60% of the docs the penalty is: x17
40% of the docs the penalty is: x13
of doing
vectorcol @@ ts_query('term & commonterm')
compared to
vectorcol @@ ts_query('term) and vectorcol @@ ts_query('commonterm');
where term is non-existing (or rare).
(in query execution performance on a fully memory recident dataset,
doing test with "drop_caches" and restart pg to simulate a dead disk the
numbers are a bit higher).
http://article.gmane.org/gmane.comp.db.postgresql.performance/22496/match=
Would you ever quantify a term sitting in 60-80% as a stop-word candidate?
I dont know if x13 in execution performance is worth hunting or there
are lower hanging fruits sitting in the fts-search-system.
This is essentially the penalty the user will get for adding a terms to
their search that rarely restricts the results.
In term of the usual "set theory" that databases work in, a search for a
stop-word translated into the full set. This is just not the case in
where it throws a warning and returns the empty set. This warning can be
caught by application code to produce the "correct" result to the users,
but just slightly more complex queries dont do this:
ftstest=# select id from ftstest where body_fts @@ to_tsquery('random |
the') limit 10;
id
----
(0 rows)
Here I would have expected the same error.. I basically have to hook in
the complete stop-word dictionary in a FTS-preparser to give the user
the expected results or have I missed a feature somwhere?
My reason for not pushing "commonterms" into the stopword list is that
they actually perform excellent in PG.
Same body as usual, but commonterm99 is sitting in 99% of the documents.
ftstest=# set enable_seqscan=off;
SET
ftstest=# explain analyze select id from ftstest where body_fts @@
to_tsquery('commonterm99');
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ftstest (cost=1051476.74..1107666.07 rows=197887
width=4) (actual time=51.036..121.348 rows=197951 loops=1)
Recheck Cond: (body_fts @@ to_tsquery('commonterm99'::text))
-> Bitmap Index Scan on ftstest_gin_idx (cost=0.00..1051427.26
rows=197887 width=0) (actual time=49.602..49.602 rows=197951 loops=1)
Index Cond: (body_fts @@ to_tsquery('commonterm99'::text))
Total runtime: 147.350 ms
(5 rows)
ftstest=# set enable_seqscan=on;
SET
ftstest=# explain analyze select id from ftstest where body_fts @@
to_tsquery('commonterm99');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on ftstest (cost=0.00..56744.00 rows=197887 width=4) (actual
time=0.086..7134.384 rows=197951 loops=1)
Filter: (body_fts @@ to_tsquery('commonterm99'::text))
Total runtime: 7194.182 ms
(3 rows)
So in order to get the result with a speedup of more than x50 I simply
cannot add these terms to the stop-words because then the first query
would resolve to an error and getting results would then be up to the
second query.
My bet is that doing a seq_scan will "never" be beneficial for this type
of query.
As far as I can see the only consequence of simply not remove stop-words
at all is a (fairly small) increase in index-size. It seems to me that
stop-words were invented when it was hard to get more than 2GB of memory
into a computer to get the index-size reduced to a size that better
could fit into memory. But nowadays it seems like the downsides are hard
to see?
Jesper
--
Jesper
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-11-03 21:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-02 22:06 Re: Queryplan within FTS/GIN index -search. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 00:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 14:56 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-03 16:49 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 17:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 21:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-11-03 18:36 ` Jesper Krogh <jesper@krogh.cc>
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