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 75AA1564AB for ; Tue, 22 Mar 2005 15:30:12 +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 68186-08 for ; Tue, 22 Mar 2005 15:30:00 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.197]) by svr1.postgresql.org (Postfix) with ESMTP id CA636563E9 for ; Tue, 22 Mar 2005 15:29:58 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id j1so1207766rnf for ; Tue, 22 Mar 2005 07:30:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=o0V3IsI+LGS6B8nOT8uJwt1eMg/joeMPStV6KmpP9MvBpm6oqdnzvY8zRVR4/YtPNg/ADsognsgEqmgEJz98eGkUJIE7JMbM9ba+UPar7AFGemIymP5GLn76mPwvvyzVL3rCULPXwQdAqUl6JUJ/mCynKlvkf/4/K07pHvu+HhE= Received: by 10.38.150.65 with SMTP id x65mr186604rnd; Tue, 22 Mar 2005 07:30:03 -0800 (PST) Received: by 10.38.126.9 with HTTP; Tue, 22 Mar 2005 07:30:03 -0800 (PST) Message-ID: Date: Tue, 22 Mar 2005 10:30:03 -0500 From: Mike Rylander Reply-To: Mike Rylander To: Rick Jansen Subject: Re: Tsearch2 performance on big database Cc: Oleg Bartunov , pgsql-performance@postgresql.org In-Reply-To: <42401CBF.50403@rockingstone.nl> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <42400F57.3040604@rockingstone.nl> <42401CBF.50403@rockingstone.nl> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.245 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/448 X-Sequence-Number: 11153 On Tue, 22 Mar 2005 14:25:19 +0100, Rick Jansen wrote: > > 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=# 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. 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. Oleg, do you see anything else on the surface here? Try: EXPLAIN ANALYZE SELECT titel FROM books WHERE idxfti @@ to_tsquery('default', 'buckingham') UNION SELECT titel FROM books WHERE idxfti @@ to_tsquery('default', 'palace'); and see if using '&' instead of '|' where you can helps out. I imagine you'd be surprised by the speed of: SELECT titel FROM books WHERE idxfti @@ to_tsquery('default', 'buckingham&palace'); > > > 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? 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 -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org