X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A74115E40BB for ; Tue, 17 Aug 2004 10:30:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69463-04 for ; Tue, 17 Aug 2004 13:30:28 +0000 (GMT) Received: from mail.aspserveur.com (7.95.39-62.rev.gaoland.net [62.39.95.7]) by svr1.postgresql.org (Postfix) with ESMTP id 1F4695E3639 for ; Tue, 17 Aug 2004 10:30:25 -0300 (ADT) Received: from saturne ([193.251.84.117]) by mail.aspserveur.com (ASPSERVEUR Ultimate mail Server) with SMTP id HUO74536 for ; Tue, 17 Aug 2004 15:30:55 +0200 Message-ID: <002901c4845e$5d1dc1c0$0c0101c0@saturne> From: "olivier HARO" To: Subject: General performance problem! Date: Tue, 17 Aug 2004 15:30:29 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0026_01C4846F.203A1450" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_FONTCOLOR_BLUE, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/253 X-Sequence-Number: 7889 This is a multi-part message in MIME format. ------=_NextPart_000_0026_01C4846F.203A1450 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have a dedicated server for my posgresql database : P4 2.4 GHZ HDD IDE 7200 rpm 512 DDR 2700 I have a problem whith one table of my database : CREATE SEQUENCE "base_aveugle_seq" START 1; CREATE TABLE "base_aveugle" ( "record_id" integer DEFAULT nextval('"base_aveugle_seq"'::text) NOT NULL, "dunsnumber" integer NOT NULL, "cp" text NOT NULL, "tel" text NOT NULL, "fax" text NOT NULL, "naf" text NOT NULL, "siege/ets" text NOT NULL, "effectif" integer NOT NULL, "ca" integer NOT NULL, Constraint "base_aveugle_pkey" Primary Key ("record_id") ); CREATE INDEX base_aveugle_dunsnumber_key ON base_aveugle USING btree (dunsn= umber); CREATE INDEX base_aveugle_cp_key ON base_aveugle USING btree (cp); CREATE INDEX base_aveugle_naf_key ON base_aveugle USING btree (naf); CREATE INDEX base_aveugle_effectif_key ON base_aveugle USING btree (effecti= f); This table contains 5 000 000 records I have a PHP application which often makes queries on this table (especiall= y on the "cp","naf","effectif" fields) Querries are like : select (distint cp) from base_aveugle where cp=3D'201A' and effectif = between 1 and 150 select (*) from base_aveugle where naf in ('721A','213F','421K') and = cp in ('54210','21459','201A') and effectif < 150 I think it is possible to optimize the performance of this queries before c= hanging the hardware (I now I will...) but I don't know how, even after hav= ing read lot of things about postgresql ... Thanks ;)=20 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.737 / Virus Database: 491 - Release Date: 11/08/2004 ------=_NextPart_000_0026_01C4846F.203A1450 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello,
 
I have a dedicated serve= r for my=20 posgresql database :
 
P4 2.4 GHZ
HDD IDE 7200 rpm<= /DIV>
512 DDR 2700
 
I have a problem whith o= ne table of=20 my database :
 
CREATE SEQUENCE "base_aveugle_seq" START= =20 1;
CREATE TABLE "base_aveugle" (
 "record_id" integer DEFAULT=20 nextval('"base_aveugle_seq"'::text) NOT NULL,
 "dunsnumber" integer= NOT=20 NULL,
 "cp" text NOT NULL,
 "tel" text NOT NULL,
 "= fax"=20 text NOT NULL,
 "naf" text NOT NULL,
 "siege/ets" text NOT= =20 NULL,
 "effectif" integer NOT NULL,
 "ca" integer NOT=20 NULL,
 Constraint "base_aveugle_pkey" Primary Key=20 ("record_id")
);
CREATE INDEX base_aveugle_dunsnumber_key ON base_ave= ugle=20 USING btree (dunsnumber);
CREATE INDEX base_aveugle_cp_key ON base_aveug= le=20 USING btree (cp);
CREATE INDEX base_aveugle_naf_key ON base_aveugle USIN= G=20 btree (naf);
CREATE INDEX base_aveugle_effectif_key ON base_aveugle USIN= G=20 btree (effectif);
 
 
This table contains 5 00= 0 000=20 records
 
I have a PHP application= which=20 often makes queries on this table (especially on the "cp","naf","effectif"= =20 fields)
 
Querries are like :
    =  =20 select (distint cp) from base_aveugle where cp=3D'201A' and effectif betwee= n 1 and=20 150
    =  =20 select (*) from base_aveugle where naf in ('721A','213F','421K') and cp in= =20 ('54210','21459','201A') and effectif < 150
 
I think it is possible t= o optimize=20 the performance of this queries before changing the hardware (I now I= =20 will...) but I don't know how, even after having read lot of things about= =20 postgresql ...
 
Thanks ;) 
 

---
Outgoing mail= is=20 certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.= 0.737 /=20 Virus Database: 491 - Release Date: 11/08/2004
------=_NextPart_000_0026_01C4846F.203A1450--