X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 69B10D1B183 for ; Mon, 2 Aug 2004 09:45:27 -0300 (ADT) 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 94705-08 for ; Mon, 2 Aug 2004 12:45:26 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7A05AD1B170 for ; Mon, 2 Aug 2004 09:45:20 -0300 (ADT) Received: from [134.22.68.4] (dyn-68-4.tor.dsl.tht.net [134.22.68.4]) by tht.net (Postfix) with ESMTP id 7E50C76A3C; Mon, 2 Aug 2004 08:45:24 -0400 (EDT) Subject: Re: No index usage with "left join" From: Rod Taylor To: mailing@impactmedia.de Cc: Postgresql Performance In-Reply-To: <20040802120851.B32852202B7@p15110922.pureserver.info> References: <20040802120851.B32852202B7@p15110922.pureserver.info> Content-Type: text/plain Message-Id: <1091450721.36221.104.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 02 Aug 2004 08:45:22 -0400 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: 200408/8 X-Sequence-Number: 7644 > SELECT count(*) FROM contacts LEFT JOIN companies ON contacts.sid = > companies.intfield01 > > contacts.sid (type text, b-tree index on it) > companies.intfield01 (type bigint, b-tree index on it) > How can I force the usage of the indexes when using "left join". Or > any other SQL construct that does the same !? Can anybody please give > us a hint !? You really don't need to use indexes since you're fetching all information from both tables. Anyway, we can be fairly sure this isn't PostgreSQL 7.4 (which would likely choose a far better plan -- hash join rather than nested loop) as it won't join a bigint to a text field without a cast. Try this: set enable_nestloop = false; SELECT count(*) FROM contacts LEFT JOIN companies ON cast(contacts.sid as bigint) = companies.intfield01; set enable_nestloop = true;