Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 622171337C06 for ; Mon, 2 May 2011 17:54:26 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69185-04-2 for ; Mon, 2 May 2011 20:54:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sam.nabble.com (sam.nabble.com [216.139.236.26]) by mail.postgresql.org (Postfix) with ESMTP id 5B0381337BD2 for ; Mon, 2 May 2011 17:54:06 -0300 (ADT) Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.69) (envelope-from ) id 1QH086-0000p4-7p for pgsql-performance@postgresql.org; Mon, 02 May 2011 13:54:06 -0700 Date: Mon, 2 May 2011 13:54:06 -0700 (PDT) From: Mark To: pgsql-performance@postgresql.org Message-ID: <1304369646236-4365717.post@n5.nabble.com> In-Reply-To: References: <1304245432864-4362578.post@n5.nabble.com> Subject: Re: Query improvement MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.767 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, URI_HEX=1.122 X-Spam-Level: X-Archive-Number: 201105/20 X-Sequence-Number: 43529 Here is EXPLAIN ANALYZE: "Limit (cost=136568.00..136568.25 rows=100 width=185) (actual time=1952.174..1952.215 rows=100 loops=1)" " -> Sort (cost=136568.00..137152.26 rows=233703 width=185) (actual time=1952.172..1952.188 rows=100 loops=1)" " Sort Key: ((ts_rank(pc.textvector, to_tsquery('fotbal'::text)) + (ts_rank(pa.titlevector, to_tsquery('fotbal'::text)) * 10::double precision)))" " Sort Method: top-N heapsort Memory: 23kB" " -> Hash Join (cost=61707.99..127636.04 rows=233703 width=185) (actual time=1046.838..1947.815 rows=3278 loops=1)" " Hash Cond: (re.rev_id = pc.old_id)" " -> Merge Join (cost=24098.90..71107.48 rows=233703 width=66) (actual time=200.884..859.453 rows=3278 loops=1)" " Merge Cond: (pa.page_id = re.rev_page)" " -> Merge Semi Join (cost=24096.98..55665.69 rows=233703 width=66) (actual time=200.843..629.821 rows=3278 loops=1)" " Merge Cond: (pa.page_id = mediawiki.page.page_id)" " -> Index Scan using page_btree_id on page pa (cost=0.00..13155.20 rows=311604 width=62) (actual time=0.027..145.989 rows=311175 loops=1)" " -> Index Scan using page_btree_id on page (cost=24096.98..38810.19 rows=233703 width=4) (actual time=200.779..429.219 rows=3278 loops=1)" " Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))" " SubPlan 1" " -> Bitmap Heap Scan on page (cost=10.41..900.33 rows=270 width=4) (actual time=0.748..9.845 rows=280 loops=1)" " Recheck Cond: (titlevector @@ to_tsquery('fotbal'::text))" " -> Bitmap Index Scan on gin_index (cost=0.00..10.34 rows=270 width=0) (actual time=0.586..0.586 rows=280 loops=1)" " Index Cond: (titlevector @@ to_tsquery('fotbal'::text))" " SubPlan 2" " -> Nested Loop (cost=1499.29..23192.08 rows=1558 width=4) (actual time=2.032..185.743 rows=3250 loops=1)" " -> Nested Loop (cost=1499.29..15967.11 rows=1558 width=4) (actual time=1.980..109.491 rows=3250 loops=1)" " -> Bitmap Heap Scan on pagecontent (cost=1499.29..6448.12 rows=1558 width=4) (actual time=1.901..36.583 rows=3250 loops=1)" " Recheck Cond: (textvector @@ to_tsquery('fotbal'::text))" " -> Bitmap Index Scan on gin_index2 (cost=0.00..1498.90 rows=1558 width=0) (actual time=1.405..1.405 rows=3250 loops=1)" " Index Cond: (textvector @@ to_tsquery('fotbal'::text))" " -> Index Scan using page_btree_rev_content_id on revision r (cost=0.00..6.10 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=3250)" " Index Cond: (r.rev_id = pagecontent.old_id)" " -> Index Scan using page_btree_id on page p (cost=0.00..4.62 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=3250)" " Index Cond: (p.page_id = r.rev_page)" " -> Index Scan using page_btree_rev_page_id on revision re (cost=0.00..11850.52 rows=311604 width=8) (actual time=0.012..166.042 rows=311175 loops=1)" " -> Hash (cost=27932.04..27932.04 rows=311604 width=127) (actual time=801.000..801.000 rows=311604 loops=1)" " Buckets: 1024 Batches: 64 Memory Usage: 744kB" " -> Seq Scan on pagecontent pc (cost=0.00..27932.04 rows=311604 width=127) (actual time=0.018..465.686 rows=311604 loops=1)" "Total runtime: 1952.962 ms" I have tried set enable_hashjoin = false; set enable_hashjoin = true; but the result have been worst than before. By the way is there a posibility to create beeter query with same effect? I have tried more queries, but this has got best performance yet. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-improvement-tp4362578p4365717.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.