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 -> 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 |
- Re: [PERFORM] Query performance problem Danilo Mota
- Re: [PERFORM] Query performance problem Tom Lane
- Re: [PERFORM] Query performance problem Bruno Wolff III