Oleg Bartunov wrote:

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:


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');
(1 row)
Time: 217395.820 ms


ilab=# explain analyze select count(*) from books where idxfti @@ to_tsquery('default', 'jane');
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

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 :)

Systems Administrator for Rockingstone IT
http://www.megabooksearch.com - Search many book listing sites at once

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to