Re: General performance problem!

Hi,

Make multi-column indexes, using the columns from your most typical queries, putting the most selective columns first (ie; you don't need to make indexes with columns in the same order as they are used in the query).

For instance, an index on cp, effectif could likely benefit both queries; same for an index on cp, effectif, naf. (You'd need only one of these indexes I think, not both. Experiment to find out which one gives you most benefit in your queries, vs. the slowdown in inserts).
Perhaps some of the single-column keys can be dropped.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of olivier HARO
Sent: dinsdag 17 augustus 2004 15:30
To: pgsql-performance(at)postgresql(dot)org
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