Re: Query performance problem - Mailing list pgsql-performance

From Bruno Wolff III
Subject Re: Query performance problem
Date
Msg-id 20040820164821.GA26674@wolff.to
Whole thread Raw
In response to Query performance problem  ("Danilo Mota" <dmota@nexen.com.br>)
Responses Re: Query performance problem  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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.

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Query performance problem
Next
From: Tom Lane
Date:
Subject: Re: Query performance problem