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