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 2A8C15E46C1 for ; Fri, 20 Aug 2004 13:20:38 -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 80276-06 for ; Fri, 20 Aug 2004 16:20:42 +0000 (GMT) Received: from hm61.locaweb.com.br (hm61.locaweb.com.br [200.213.197.161]) by svr1.postgresql.org (Postfix) with SMTP id 69D565E46C0 for ; Fri, 20 Aug 2004 13:20:30 -0300 (ADT) Received: (qmail 4309 invoked from network); 20 Aug 2004 16:19:11 -0000 Received: from unknown (10.1.10.20) by hm61.locaweb.com.br with QMTP; 20 Aug 2004 16:19:11 -0000 Received: (qmail 8378 invoked from network); 20 Aug 2004 16:20:32 -0000 Received: from unknown (HELO xws08) (dmota@nexen.com.br@201.9.66.167) by hm20.locaweb.com.br with SMTP; 20 Aug 2004 16:20:32 -0000 Reply-To: From: "Danilo Mota" To: Subject: Query performance problem Date: Fri, 20 Aug 2004 13:25:30 -0300 Organization: Nexen Tecnologia Message-ID: <002201c486d2$4fbe4420$8afea8c0@nexen.lan> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0023_01C486B9.2A737D20" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_70_80, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/304 X-Sequence-Number: 7940 This is a multi-part message in MIME format. ------=_NextPart_000_0023_01C486B9.2A737D20 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit HI All, I have a big performance issue concerning a PostgreSQL database. I have the following server configuration: Pentium 4 2.4 GHz 1 GB RAM 36 GB SCSI 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; The problem is, it takes a long time to run, I wait up to half an hour and I get no result. So, I executed the explain on the query and got the following results: QUERY PLAN ------------------------------------------------------------------------ -- Nested Loop (cost=0.00..16696.87 rows=75816 width=4) -> Seq Scan on sav_cliente_cf sc (cost=0.00..3047.55 rows=1 width=0) Filter: ((cpfcnpj)::text = (cpfcnpj)::text) -> Seq Scan on r_cliente rc (cost=0.00..12891.16 rows=75816 width=4) And made the following modifications on my POSTGRESQL.CONF file: POSTGRESQL.CONF ------------------------------------------------------------------------ -- ### VERSION: Postgresql 7.4.2 ### shared_buffers = 7800 sort_mem = 4096 checkpoint_segments = 5 effective_cache_size = 12000 cpu_operator_cost = 0.0015 stats_start_collector = false Hope you can help me, I really need to get this running faster, and I am out of ideas. Since now, thanks a lot for your attention, Danilo Mota ------=_NextPart_000_0023_01C486B9.2A737D20 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

HI All,

 

I have a big performance issue concerning a PostgreSQL database.

 

I have the following server configuration:

 

Pentium 4 2.4 GHz

1 GB RAM

36 GB SCSI

 <= /font>

And the following tables:

        =             &nb= sp;            =    TABLES

--------------------------------------------------------= ------------------

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  r_cliente: 75816 records  =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D

CREATE TABLE "public".&qu= ot;r_cliente" (

  "pkcliente" INTEGER NO= T NULL,

  "cpfcnpj" VARCHAR(20) = NOT NULL,

  PRIMARY <= span class=3DGramE>KEY("pkcliente")=

) WITH OIDS;

 

CREATE UNIQUE INDEX "un_cliente_cpfcnpj" ON "public"."r_cliente"

USING btree ("cpfcnpj");

 

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  sav_cliente_lg: 65671 records  =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D

CREATE TABLE "public".&qu= ot;sav_cliente_lg" (

  "codigo" INTEGER NOT N= ULL,

  "cpfcnpj" VARCHAR(15) = NOT NULL,

  PRIMARY <= span class=3DGramE>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:

 

        =             &nb= sp;            =    QUERY

--------------------------------------------------------= ------------------

SELECT

 rc.pkcliente

FROM r_cliente AS rc

INNER JOIN sav_cliente_lg AS= sc ON sc.cpfcnpj =3D sc.cpfcnpj<= /span>;

 

 

 

 

The problem is, it takes a long time to run, I wait up to half an hour and I get no result.

 

So, I executed the explain on= the query and got the following results:

 

 

 

 

        =             &nb= sp;           QUER= Y PLAN

--------------------------------------------------------= ------------------

 Nested Loop  (cost=3D0.00= ..16696.87 rows=3D75816 width=3D4)

   ->  Seq Scan on sav_cliente_cf sc&nb= sp; (cost=3D0.00..3047.55 rows=3D1 width=3D0)

    = ;     Filter: ((cpfcnpj)::text =3D (cpfcnpj)::text)

   ->  Seq Scan on r_cliente rc  (cost=3D0.00..12891.16 rows=3D7581= 6 width=3D4)

 

 

 

 

And made the following modific= ations on my POSTGRESQL.CONF file:

 

        =             &nb= sp;           POST= GRESQL.CONF

--------------------------------------------------------= ------------------

### VERSION: Postgresql 7.4.= 2 ###

shared_buffers = =3D 7800

sort_mem<= font size=3D2 face=3DArial> = =3D 4096

checkpoint_segments = =3D 5 

effective_cache_size = =3D 12000

cpu_operator_cost = =3D 0.0015

stats_start_collector = =3D false

 

 

Hope you can help me, I really need to get this running faster, and I am out of ideas.

 

Since now, thanks a lot for your attention,

 

Danilo <= span class=3DSpellE>Mota

------=_NextPart_000_0023_01C486B9.2A737D20--