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 53C8B5E46D8 for ; Fri, 20 Aug 2004 13:39:51 -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 86039-08 for ; Fri, 20 Aug 2004 16:39:56 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id AC0FD5E3F15 for ; Fri, 20 Aug 2004 13:39:45 -0300 (ADT) Received: (qmail 26987 invoked by uid 500); 20 Aug 2004 16:48:21 -0000 Date: Fri, 20 Aug 2004 11:48:21 -0500 From: Bruno Wolff III To: Danilo Mota Cc: pgsql-performance@postgresql.org Subject: Re: Query performance problem Message-ID: <20040820164821.GA26674@wolff.to> Mail-Followup-To: Danilo Mota , pgsql-performance@postgresql.org References: <002201c486d2$4fbe4420$8afea8c0@nexen.lan> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <002201c486d2$4fbe4420$8afea8c0@nexen.lan> User-Agent: Mutt/1.5.6i 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/306 X-Sequence-Number: 7942 On Fri, Aug 20, 2004 at 13:25:30 -0300, Danilo Mota wrote: > > And the following tables: > TABLES > ------------------------------------------------------------------------ > -- > ================== r_cliente: 75816 records > ============================ > CREATE TABLE "public"."r_cliente" ( > "pkcliente" INTEGER NOT NULL, > "cpfcnpj" VARCHAR(20) NOT NULL, > PRIMARY KEY("pkcliente") > ) WITH OIDS; > > CREATE UNIQUE INDEX "un_cliente_cpfcnpj" ON "public"."r_cliente" > USING btree ("cpfcnpj"); > > ================== sav_cliente_lg: 65671 records > ======================= > CREATE TABLE "public"."sav_cliente_lg" ( > "codigo" INTEGER NOT NULL, > "cpfcnpj" VARCHAR(15) NOT NULL, > PRIMARY KEY("codigo") > ) WITH OIDS; > > CREATE INDEX "ix_savclientelg_cpfcnpj" ON "public"."sav_cliente_lg" > USING btree ("cpfcnpj"); > > > > Which I would like to run the following query: > > QUERY > ------------------------------------------------------------------------ > -- > SELECT > rc.pkcliente > FROM r_cliente AS rc > INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj; I am going to assume that one of the sc.cpfcnpj's above is really rc.cpfcnpj since that corresponds to the explain below. sc.cpfcnpj and rc.cpfcnpj are different length varchars. You made need an explicit cast to allow the use of indexes. (Unless there is a real business rule that mandates the limits you have used, you probably want to make them both type 'text'.) Another potential problem is not having analyzed the tables. I don't think this can be ruled out based on what you have showed us so far. > > So, I executed the explain on the query and got the following results: Generally you want to run EXPLAIN ANALYZE results when submitting questions about performance problems rather than just EXPLAIN results.