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 A11C45E46EF for ; Tue, 17 Aug 2004 11:12:31 -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 85710-10 for ; Tue, 17 Aug 2004 14:12:24 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id BFBA95E46CC for ; Tue, 17 Aug 2004 11:12:21 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i7HECMiI090585 for ; Tue, 17 Aug 2004 14:12:22 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i7HDmQF7078750 for pgsql-performance@postgresql.org; Tue, 17 Aug 2004 13:48:26 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: General performance problem! Date: Tue, 17 Aug 2004 15:48:29 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 62 Message-ID: <41220CAD.60401@bigfoot.com> References: <002901c4845e$5d1dc1c0$0c0101c0@saturne> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: olivier HARO User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en In-Reply-To: <002901c4845e$5d1dc1c0$0c0101c0@saturne> X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/255 X-Sequence-Number: 7891 olivier HARO wrote: > 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 > (dunsnumber); > 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 > (effectif); > > > This table contains 5 000 000 records > > I have a PHP application which often makes queries on this table > (especially on the "cp","naf","effectif" fields) > > Querries are like : > select (distint cp) from base_aveugle where cp='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 changing the hardware (I now I will...) but I don't know how, > even after having read lot of things about postgresql ... Show us a explain analyze for that queries. Regards Gaetano Mendola