Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group