X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3D7A69DC861 for ; Sun, 15 Jan 2006 19:35:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83219-08 for ; Sun, 15 Jan 2006 19:35:09 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp1.iitb.ac.in (unknown [203.199.81.249]) by postgresql.org (Postfix) with SMTP id ACC8F9DC816 for ; Sun, 15 Jan 2006 19:35:03 -0400 (AST) Received: (qmail 10493 invoked from network); 16 Jan 2006 05:05:05 +0530 Received: from unknown (HELO ldns2.iitb.ac.in) (10.200.12.2) by smtp1.iitb.ac.in with SMTP; 16 Jan 2006 05:05:05 +0530 Received: (qmail 8474 invoked by uid 509); 15 Jan 2006 23:35:05 -0000 Received: from 10.105.1.1 by ldns2 (envelope-from , uid 501) with qmail-scanner-1.25st (clamdscan: 0.87.1/1242. spamassassin: 3.1.0. Clear:RC:1(10.105.1.1):. Processed in 0.021911 secs); 15 Jan 2006 23:35:05 -0000 Received: from unknown (HELO cse.iitb.ac.in) (10.105.1.1) by ldns2.iitb.ac.in with SMTP; 15 Jan 2006 23:35:05 -0000 Received: (qmail 31964 invoked by uid 11940); 15 Jan 2006 23:37:21 -0000 Received: from 10.105.11.33 by jeeves.cse.iitb.ac.in (envelope-from , uid 11926) with qmail-scanner-1.25 (clamdscan: 0.87/1243. spamassassin: 3.1.0. Clear:RC:1(10.105.11.33):. Processed in 0.036292 secs); 15 Jan 2006 23:37:21 -0000 Received: from nsl-33.cse.iitb.ac.in ([10.105.11.33]) (envelope-sender ) by cse.iitb.ac.in (qmail-ldap-1.03) with SMTP for ; 15 Jan 2006 23:37:21 -0000 Date: Mon, 16 Jan 2006 05:05:04 +0530 (IST) From: "Anjan Kumar. A." To: pgsql-hackers@postgresql.org Subject: Problem with the Planner In-Reply-To: Message-ID: References: <17378.1134321553@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/444 X-Sequence-Number: 78548 Please observe the following queries. Why PostgreSQL is favouring MergeJoin eventhough, it leading to higher execution times than NestedLoopJoin. Any suggestions to fix this problem. bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=665.09..4704.60 rows=166701 width=488) (actual time=10.128..40.843 rows=50 loops=1) Merge Cond: ("outer".unique2 = "inner".unique2) -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..1514.00 rows=10000 width=244) (actual time=0.031..20.520 rows=10000 loops=1) -> Sort (cost=665.09..673.42 rows=3334 width=244) (actual time=9.601..9.646 rows=50 loops=1) Sort Key: t1.unique2 -> Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.154..9.140 rows=50 loops=1) Filter: (unique1 < 50) Total runtime: 41.101 ms (8 rows) bench=# SET enable_mergejoin = off; SET bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Hash Join (cost=588.34..11841.35 rows=166701 width=488) (actual time=9.028..70.453 rows=50 loops=1) Hash Cond: ("outer".unique2 = "inner".unique2) -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..11.846 rows=10000 loops=1) -> Hash (cost=470.00..470.00 rows=3334 width=244) (actual time=8.378..8.378 rows=0 loops=1) -> Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.093 rows=50 loops=1) Filter: (unique1 < 50) Total runtime: 70.659 ms (7 rows) bench=# SET enable_hashjoin = off; SET bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.00..633218.15 rows=166701 width=488) (actual time=0.178..9.389 rows=50 loops=1) -> Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.349 rows=50 loops=1) Filter: (unique1 < 50) -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..189.16 rows=50 width=244) (actual time=0.009..0.011 rows=1 loops=50) Index Cond: ("outer".unique2 = t2.unique2) Total runtime: 9.552 ms (6 rows) -- Regards. Anjan Kumar A. MTech2, Comp Sci., www.cse.iitb.ac.in/~anjankumar ______________________________________________________________