From: Rick Jansen <rick@rockingstone.nl>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: Mike Rylander <mrylander@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 performance on big database
Date: Tue, 22 Mar 2005 17:05:55 +0100
Message-ID: <42404263.40604@rockingstone.nl> (raw)
In-Reply-To: <Pine.GSO.4.62.0503221840480.5508@ra.sai.msu.su>
References: <42400F57.3040604@rockingstone.nl>
<Pine.GSO.4.62.0503221533400.5508@ra.sai.msu.su>
<b918cf3d050322044847d3f9e1@mail.gmail.com>
<42401CBF.50403@rockingstone.nl>
<b918cf3d0503220730665fe92c@mail.gmail.com>
<Pine.GSO.4.62.0503221840480.5508@ra.sai.msu.su>
Oleg Bartunov wrote:
> Mike,
>
> no comments before Rick post tsearch configs and increased buffers !
> Union shouldn't be faster than (term1|term2).
> tsearch2 internals description might help you understanding tsearch2
> limitations.
> See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
> Also, don't miss my notes:
> http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
>
> Oleg
Thanks Oleg, i've seen those pages before :) I've set shared_buffers to
45000 now (yes thats probably very much, isn't it?) and it already seems
a lot quicker.
How do I find out what my tsearch config is? I followed the intro
(http://www.sai.msu.su/~megera/oddmuse/index.cgi/tsearch-v2-intro) and
applied it to our books table, thats all, didnt change anything else
about configs.
> On Tue, 22 Mar 2005, Mike Rylander wrote:
>> Ahh... I should have qualified my claim. I am creating a google-esqe
>> search interface and almost every query uses '&' as the term joiner.
>> 'AND' queries and one-term queries are orders of magnitude faster than
>> 'OR' queries, and fortunately are the expected default for most users.
>> (Think, "I typed in these words, therefore I want to match these
>> words"...) An interesting test may be to time multiple queries
>> independently, one for each search term, and see if the combined cost
>> is less than a single 'OR' search. If so, you could use UNION to join
>> the results.
Well I just asked my colleges and OR queries arent used by us anyway, so
I'll test for AND queries instead.
>> However, the example you originally gave ('terminology') should be
>> very fast. On a comparable query ("select count(value) from
>> metabib.full_rec where index_vector @@ to_tsquery('default','jane');")
>> I get 12ms.
ilab=# select count(*) from books where idxfti @@ to_tsquery('default',
'jane');
count
-------
4093
(1 row)
Time: 217395.820 ms
:(
ilab=# explain analyze select count(*) from books where idxfti @@
to_tsquery('default', 'jane');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16591.95..16591.95 rows=1 width=0) (actual
time=4634.931..4634.932 rows=1 loops=1)
-> Index Scan using idxfti_idx on books (cost=0.00..16581.69
rows=4102 width=0) (actual time=0.395..4631.454 rows=4093 loops=1)
Index Cond: (idxfti @@ '\'jane\''::tsquery)
Total runtime: 4635.023 ms
(4 rows)
Time: 4636.028 ms
ilab=#
>> 16G of RAM on a dedicated machine.
>>
>>
>> shared_buffers = 15000 # min 16, at least max_connections*2,
>> 8KB each
>> work_mem = 10240 # min 64, size in KB
>> maintenance_work_mem = 1000000 # min 1024, size in KB
>> # big m_w_m for loading data...
>>
>> random_page_cost = 2.5 # units are one sequential page fetch
>> cost
>> # fast drives, and tons of RAM
>>
Right.. well I'll try copying these settings, see how that works out,
thanks :)
Rick
--
Systems Administrator for Rockingstone IT
http://www.rockingstone.comhttp://www.megabooksearch.com - Search many book listing sites at once
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: rick@rockingstone.nl, oleg@sai.msu.su, mrylander@gmail.com
Subject: Re: Tsearch2 performance on big database
In-Reply-To: <42404263.40604@rockingstone.nl>
* 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