From: Danilo Mota <dmota@nexen.com.br>
To: pgsql-performance@postgresql.org
Subject: Query performance problem
Date: Fri, 20 Aug 2004 13:25:30 -0300
Message-ID: <002201c486d2$4fbe4420$8afea8c0@nexen.lan> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmota@nexen.com.br
Subject: Re: Query performance problem
In-Reply-To: <002201c486d2$4fbe4420$8afea8c0@nexen.lan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox