Re: Full Text Search ideas

On Mon, Jul 19, 2010 at 6:16 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Howard Rogers <hjr(at)diznix(dot)com> writes:
>> ims=# select count(*) from search_rm
>> where to_tsvector('english', textsearch) @@ to_tsquery('english','bat & sb12n');
>> count
>> -------
>>  3849
>> (1 row)
>
>> Time: 408.962 ms
>
>> ims=# select count(*) from search_rm
>> where to_tsvector('english', textsearch) @@ to_tsquery('english','bat & !sb12y');
>> count
>> -------
>>  3849
>> (1 row)
>
>> Time: 11.533 ms
>
> Yeah, I imagine that the first of these will involve examining all the
> index entries for sb12n.  There's not a lot of smarts about that inside
> the GIN index machinery, AFAIK: it'll just fetch all the relevant TIDs
> for both terms and then AND them.
>
>> I'm wondering firstly if there's any way I can configure PostgreSQL
>> FTS so that it produces the sort of results we've gotten used to from
>> Oracle, i.e., where search speeds do not go up wildly when a 'search
>> term' is applied that happens to be used by the vast majority of
>> document records.
>
> If you're willing to split out the search terms that are like this,
> you could probably get better results with something like
>
> select count(*) from search_rm
> where to_tsvector('english', textsearch) @@ to_tsquery('english','bat') AND
>      to_tsvector('english', textsearch) @@ to_tsquery('english','sb12n');
>
> That will put it on the optimizer's head as to whether to use the index
> for one term or both terms.
>
> It might be worth noting that the optimizer will of course not get this
> right unless it has decent statistics about both search terms --- and
> there is an as-yet-unreleased patch about tsvector stats gathering:
> http://archives.postgresql.org/pgsql-committers/2010-05/msg00360.php
> I am not sure that the situation addressed by that patch applies in
> your case, but it might.
>
>                        regards, tom lane
>

Thanks, Tom.

The breaking out into separate search terms does make a difference,
but not much:

ims=# select count(*) from search_rm
where to_tsvector('english',textsearch) @@ to_tsquery('english','bat & sb12n');
count
-------
3849
(1 row)

Time: 413.329 ms

ims=# select count(*) from search_rm
ims-# where to_tsvector('english',textsearch) @@ to_tsquery('english','bat') AND
ims-# to_tsvector('english',textsearch) @@ to_tsquery('english','sb12n');
count
-------
3849
(1 row)

Time: 352.583 ms

So it's shaving about a sixth of the time off, which isn't bad, but
not spectacularly good either!

I'd also thought of trying something like this:

ims=# select count(*) from
(
select * from search_rm where
to_tsvector('english',textsearch) @@ to_tsquery('english','bat')
) as core
where to_tsvector('english',textsearch) @@ to_tsquery('english','sb12n');

count
-------
3849
(1 row)

Time: 357.248 ms

...in the hope that the sb12n test would only be applied to the set of
'bat' records acquired by the inner query. But as you can tell from
the time, that's not particularly better or worse than your suggestion
(bearing mind that 'bat' on its own is a 12ms search).

I'm currently constructing a separate column containing a single
bitmask value for about 15 of the 45 attributes, just to see if
evaluating the bits with a bitand test for the bat records is faster
than trying to FTS them in the first place. Something like

select count (*) from
(
select * from search_rm where
to_tsvector('english',textsearch) @@ to_tsquery('english','bat')
) as core
where bitand(searchbits,4096)>0;

But it's taking a while to get that extra column constructed in the
original table!

Fingers crossed, because if not, it's all a bit of a show-stopper for
our migration effort, I think. :-(

Regards & thanks
HJR