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 163EE9DC941 for ; Sun, 15 Jan 2006 20:20:24 -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 06214-04 for ; Sun, 15 Jan 2006 20:20:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from linuxworld.com.au (unknown [203.34.46.50]) by postgresql.org (Postfix) with ESMTP id 6CF0C9DC854 for ; Sun, 15 Jan 2006 20:20:20 -0400 (AST) Received: from linuxworld.com.au (IDENT:swm@localhost.localdomain [127.0.0.1]) by linuxworld.com.au (8.13.2/8.13.2) with ESMTP id k0G0KG7A030688; Mon, 16 Jan 2006 11:20:16 +1100 Received: from localhost (swm@localhost) by linuxworld.com.au (8.13.2/8.13.2/Submit) with ESMTP id k0G0KGc4030685; Mon, 16 Jan 2006 11:20:16 +1100 Date: Mon, 16 Jan 2006 11:20:16 +1100 (EST) From: Gavin Sherry To: "Anjan Kumar. A." cc: pgsql-hackers@postgresql.org Subject: Re: 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 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.017 required=5 tests=[AWL=0.017] X-Spam-Score: 0.017 X-Spam-Level: X-Archive-Number: 200601/446 X-Sequence-Number: 78550 On Mon, 16 Jan 2006, Anjan Kumar. A. wrote: > > > > 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) Your statistics are way off. The seqscan on tenk1 estimates 3334 rows but gets only 50. Run ANALYZE and try again. Thanks, Gavin