X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7E668569B0 for ; Tue, 22 Mar 2005 16:06:06 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81067-01 for ; Tue, 22 Mar 2005 16:06:00 +0000 (GMT) Received: from mx1.rockingstone.nl (mx1.rockingstone.nl [82.94.241.100]) by svr1.postgresql.org (Postfix) with ESMTP id BACA85670A for ; Tue, 22 Mar 2005 16:05:58 +0000 (GMT) Received: from localhost (web1 [127.0.0.1]) by mx1.rockingstone.nl (Postfix) with ESMTP id BBF56FB0AB; Tue, 22 Mar 2005 17:12:01 +0100 (CET) Received: from mx1.rockingstone.nl ([127.0.0.1]) by localhost (web1 [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26739-11; Tue, 22 Mar 2005 17:12:00 +0100 (CET) Received: from [10.10.20.219] (217-19-26-53.dsl.cambrium.nl [217.19.26.53]) by mx1.rockingstone.nl (Postfix) with ESMTP id 02890FB074; Tue, 22 Mar 2005 17:11:59 +0100 (CET) Message-ID: <42404263.40604@rockingstone.nl> Date: Tue, 22 Mar 2005 17:05:55 +0100 From: Rick Jansen User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Oleg Bartunov Cc: Mike Rylander , pgsql-performance@postgresql.org Subject: Re: Tsearch2 performance on big database References: <42400F57.3040604@rockingstone.nl> <42401CBF.50403@rockingstone.nl> In-Reply-To: X-Enigmail-Version: 0.90.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at rockingstone.nl X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/451 X-Sequence-Number: 11156 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.com http://www.megabooksearch.com - Search many book listing sites at once