Re: Query performance problem

On Fri, Aug 20, 2004 at 13:25:30 -0300,
Danilo Mota <dmota(at)nexen(dot)com(dot)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.