X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 99C09329F1A for ; Mon, 27 Sep 2004 19:56:43 +0100 (BST) 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 93439-09 for ; Mon, 27 Sep 2004 18:56:37 +0000 (GMT) Received: from mail.musicrebellion.com (dsl093-203-229.ind1.dsl.speakeasy.net [66.93.203.229]) by svr1.postgresql.org (Postfix) with ESMTP id 5C566329E8E for ; Mon, 27 Sep 2004 19:56:37 +0100 (BST) Received: from [10.0.1.102] (helo=musicrebellion.com ident=kris) by mail.musicrebellion.com with asmtp (Exim 4.34 #1 (Debian)) id 1CC0fq-00029J-EF for ; Mon, 27 Sep 2004 13:56:34 -0500 Message-ID: <41586264.5020302@musicrebellion.com> Date: Mon, 27 Sep 2004 13:56:36 -0500 From: Kris Kiger User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020623 Debian/1.0.0-0.woody.1 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: tsearch2 poor performance References: <71E37EF6B7DCC1499CEA0316A2568328DC9DF0@loki.wc.globexplorer.net> <4158409E.8060109@musicrebellion.com> <41584E49.5040301@musicrebellion.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/256 X-Sequence-Number: 14959 Yes, it is much better than no index of sequential scan. We may just be looking at the best performance tsearch2 can offer on my machine. search_test=# explain analyze SELECT count(q) FROM product, to_tsquery('oil') AS q WHERE vector @@ q; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=67847264.50..67847264.50 rows=1 width=32) (actual time=83311.552..83311.555 rows=1 loops=1) -> Nested Loop (cost=12.50..67839764.50 rows=3000001 width=32) (actual time=0.204..81960.198 rows=226357 loops=1) Join Filter: ("outer".vector @@ "inner".q) -> Seq Scan on product (cost=0.00..339752.00 rows=3000000 width=32) (actual time=0.100..27415.795 rows=3000000 loops=1) -> Materialize (cost=12.50..22.50 rows=1000 width=32) (actual time=0.003..0.006 rows=1 loops=3000000) -> Function Scan on q (cost=0.00..12.50 rows=1000 width=32) (actual time=0.020..0.024 rows=1 loops=1) Total runtime: 83311.735 ms (7 rows) search_test=# explain analyze select count(*) from product where description like '% oil %'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=347264.01..347264.01 rows=1 width=0) (actual time=39858.350..39858.353 rows=1 loops=1) -> Seq Scan on product (cost=0.00..347252.00 rows=4801 width=0) (actual time=0.100..38320.293 rows=226357 loops=1) Filter: (description ~~ '% oil %'::text) Total runtime: 39858.491 ms >>Oleg, >> >> Thanks for the help on this. >> >> The query I used to return the 508 number is: >> SELECT * FROM stat('SELECT vector FROM product') ORDER BY ndoc >>desc, word ; >> >> Testing says, the more words I use, the faster the query is. My >>original search word, 'oil', appears in 226,357 documents 233,266 times. >> As far as distinct words go, 'oil' is middle of the road for >>occurences. As it is set up now, the best search time I am getting on >>this single word is roughly 22 seconds. >> >> > >Does this time (22 seconds) is still better than seq. scan (no index) >or standard 'LIKE' ? > > >