Re: [PERFORM] General performance problem!

[Duane Lee - EGOVX]

An index on cp and effectif would help your first query.  An index on naf, cp and effectif would help your second query.   Something like this:   CREATE INDEX base_aveugle_cp_key2 ON base_aveugle USING btree (cp, effectif); CREATE INDEX base_aveugle_naf_key2 ON base_aveugle USING btree (naf, cp, effectif);   Another thing, why include “distinct cp” when you are only selecting “cp=’201A’”?  You will only retrieve one record regardless of how many may contain cp=’201A’.   If you could make these UNIQUE indexes that would help also but it’s not a requirement.   Good luck, Duane     -----Original Message----- From: olivier HARO [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 17, 2004 6:30 AM To: [EMAIL PROTECTED] Subject: [PERFORM] General performance problem!   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 ...   Thanks ;)    --- 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