From: Bruno Wolff III <bruno@wolff.to>
To: Danilo Mota <dmota@nexen.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance problem
Date: Fri, 20 Aug 2004 11:48:21 -0500
Message-ID: <20040820164821.GA26674@wolff.to> (raw)
In-Reply-To: <002201c486d2$4fbe4420$8afea8c0@nexen.lan>
References: <002201c486d2$4fbe4420$8afea8c0@nexen.lan>
On Fri, Aug 20, 2004 at 13:25:30 -0300,
Danilo Mota <dmota@nexen.com.br> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, dmota@nexen.com.br
Subject: Re: Query performance problem
In-Reply-To: <20040820164821.GA26674@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox