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.com 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])
