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 D3BAF564E7 for ; Tue, 22 Mar 2005 13:25:26 +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 26238-03 for ; Tue, 22 Mar 2005 13:25:18 +0000 (GMT) Received: from mx1.rockingstone.nl (mx1.rockingstone.nl [82.94.241.100]) by svr1.postgresql.org (Postfix) with ESMTP id BFAE856529 for ; Tue, 22 Mar 2005 13:25:17 +0000 (GMT) Received: from localhost (web1 [127.0.0.1]) by mx1.rockingstone.nl (Postfix) with ESMTP id 07186FB0A8; Tue, 22 Mar 2005 14:31:25 +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 05839-14; Tue, 22 Mar 2005 14:31:23 +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 C8F49FB069; Tue, 22 Mar 2005 14:31:23 +0100 (CET) Message-ID: <42401CBF.50403@rockingstone.nl> Date: Tue, 22 Mar 2005 14:25:19 +0100 From: Rick Jansen User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Mike Rylander Cc: Oleg Bartunov , pgsql-performance@postgresql.org Subject: Re: Tsearch2 performance on big database References: <42400F57.3040604@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/444 X-Sequence-Number: 11149 Mike Rylander wrote: > On Tue, 22 Mar 2005 15:36:11 +0300 (MSK), Oleg Bartunov wrote: >> >>use something like >>echo "150000000" > /proc/sys/kernel/shmmax >>to increase shared memory. In your case you could dedicate much more >>memory. >> >> Regards, >> Oleg Thanks, I'll check that out. > And Oleg should know. Unless I'm mistaken, he (co)wrote tsearch2. > Other than shared buffers, I can't imagine what could be causing that > kind of slowness. EXPLAIN ANALYZE, please? > ilab=# explain analyze select count(titel) from books where idxfti @@ to_tsquery('default', 'buckingham | palace'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=35547.99..35547.99 rows=1 width=56) (actual time=125968.119..125968.120 rows=1 loops=1) -> Index Scan using idxfti_idx on books (cost=0.00..35525.81 rows=8869 width=56) (actual time=0.394..125958.245 rows=3080 loops=1) Index Cond: (idxfti @@ '\'buckingham\' | \'palac\''::tsquery) Total runtime: 125968.212 ms (4 rows) Time: 125969.264 ms ilab=# > As an example of what I think you *should* be seeing, I have a similar > box (4 procs, but that doesn't matter for one query) and I can search > a column with tens of millions of rows in around a second. > That sounds very promising, I'd love to get those results.. could you tell me what your settings are, howmuch memory you have and such? Thanks. Rick -- Systems Administrator for Rockingstone IT http://www.rockingstone.com http://www.megabooksearch.com - Search many book listing sites at once