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 5BD0232A1BF for ; Fri, 1 Oct 2004 23:02:05 +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 73073-05 for ; Fri, 1 Oct 2004 22:01:54 +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 1A00632A1FB for ; Fri, 1 Oct 2004 23:01:53 +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 1CDVTG-0003iB-5j for ; Fri, 01 Oct 2004 17:01:46 -0500 Message-ID: <415DD3CC.3030208@musicrebellion.com> Date: Fri, 01 Oct 2004 17:01:48 -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> <41586264.5020302@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: 200410/38 X-Sequence-Number: 15032 Hey all, its me again. If I do not do a count(product_id) on my tsearch2 queries, its actually really fast, for example; explain analyze SELECT product_id FROM product, to_tsquery('bear') AS q WHERE vector @@ q LIMIT 1000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..2081.60 rows=1000 width=4) (actual time=2.308..51.522 rows=1000 loops=1) -> Nested Loop (cost=0.00..6244798.55 rows=3000001 width=4) (actual time=2.299..45.637 rows=1000 loops=1) -> Function Scan on q (cost=0.00..12.50 rows=1000 width=32) (actual time=0.023..0.023 rows=1 loops=1) -> Index Scan using vector_idx on product (cost=0.00..6207.29 rows=3000 width=36) (actual time=2.253..37.946 rows=1000 loops=1) Index Cond: (product.vector @@ "outer".q) Filter: (product.vector @@ "outer".q) Total runtime: 122.487 ms explain analyze SELECT product_id FROM product, to_tsquery('complex') AS q WHERE vector @@ q LIMIT 1000; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..2081.60 rows=1000 width=4) (actual time=4.943..2325.949 rows=1000 loops=1) -> Nested Loop (cost=0.00..6244798.55 rows=3000001 width=4) (actual time=4.933..2319.885 rows=1000 loops=1) -> Function Scan on q (cost=0.00..12.50 rows=1000 width=32) (actual time=0.040..0.040 rows=1 loops=1) -> Index Scan using vector_idx on product (cost=0.00..6207.29 rows=3000 width=36) (actual time=4.868..2311.087 rows=1000 loops=1) Index Cond: (product.vector @@ "outer".q) Filter: (product.vector @@ "outer".q) Total runtime: 2329.389 ms From what I know, there is only one reason I can offer why a count takes approximately 30~40 seconds longer on these same queries... that is that count has to evaluate whether a value is null or not. There probably is a better reason, if anyone has any ideas, I would much appreciate you sharing! Also, why the big difference in query times in the above? bear appears 780963 times in 696668 documents complex appears 468669 times in 440339 documents. Again, thanks all! Kris