Query performance problem

From: "Danilo Mota" <dmota(at)nexen(dot)com(dot)br>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query performance problem
Date: 2004-08-20 16:25:30
Message-ID: 002201c486d2$4fbe4420$8afea8c0@nexen.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2004-08-20 16:34:48 Re: Query performance problem
Previous Message Tom Lane 2004-08-20 16:19:22 Re: using an index worst performances