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 00F2DD1B171 for ; Mon, 2 Aug 2004 09:38:46 -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 94813-02 for ; Mon, 2 Aug 2004 12:38:45 +0000 (GMT) Received: from tino.sinectis.com.ar (tino.sinectis.com.ar [216.244.192.232]) by svr1.postgresql.org (Postfix) with ESMTP id B35EFD1B184 for ; Mon, 2 Aug 2004 09:38:40 -0300 (ADT) Received: by tino.sinectis.com.ar (Postfix, from userid 99) id A10196C8BB; Mon, 2 Aug 2004 09:38:42 -0300 (GMT+3) Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 X-Mailer: Sinectis Webmail 5.6.16-1.5.1 From: G u i d o B a r o s i o To: tim.leeuwvander@nl.unisys.com, mailing@impactmedia.de, pgsql-performance@postgresql.org Subject: Re: No index usage with Reply-To: gbarosio@uolsinectis.com.ar Message-Id: <20040802123842.A10196C8BB@tino.sinectis.com.ar> Date: Mon, 2 Aug 2004 09:38:42 -0300 (GMT+3) 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/7 X-Sequence-Number: 7643 TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Greetz, Guido > Cannot you do a cast in your query? Does that help with using the indexes? > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of > mailing@impactmedia.de > Sent: maandag 2 augustus 2004 14:09 > To: pgsql-performance@postgresql.org > Subject: [PERFORM] No index usage with "left join" > > > We have a "companies" and a "contacts" table with about 3000 records > each. > > We run the following SQL-Command which runs about 2 MINUTES !: > > 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) > > comfire=> explain analyze SELECT count(*) FROM prg_contacts LEFT JOIN > prg_addresses ON prg_contacts.sid=prg_addresses.intfield01; > NOTICE: QUERY PLAN: > > Aggregate (cost=495261.02..495261.02 rows=1 width=15) (actual > time=40939.38..40939.38 rows=1 loops=1) > -> Nested Loop (cost=0.00..495253.81 rows=2885 width=15) (actual > time=0.05..40930.14 rows=2866 loops=1) > -> Seq Scan on prg_contacts (cost=0.00..80.66 rows=2866 > width=7) (actual time=0.01..18.10 rows=2866 loops=1) > -> Seq Scan on prg_addresses (cost=0.00..131.51 rows=2751 > width=8) (actual time=0.03..6.25 rows=2751 loops=2866) > Total runtime: 40939.52 msec > > EXPLAIN > > Note: > - We need the left join because we need all contacts even if they are > not assigned to a company > - We are not able to change the datatypes of the joined fields > because we use a standard software (btw who cares: SuSE Open Exchange > Server) > - When we use a normal join (without LEFT or a where clause) the SQL > runs immediately using the indexes > > 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 !? > > Thanks in forward. > > Greetings > Achim > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match