pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: olivier HARO <o.haro@en-compro.com>
To: pgsql-performance@postgresql.org
Subject: General performance problem!
Date: Tue, 17 Aug 2004 15:30:29 +0200
Message-ID: <002901c4845e$5d1dc1c0$0c0101c0@saturne> (raw)

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

view thread (6+ messages)  latest in thread

Message-ID: <002901c4845e$5d1dc1c0$0c0101c0@saturne>
Permalink:  ../002901c4845e$5d1dc1c0$0c0101c0@saturne/
Also on:    postgresql.org/message-id/002901c4845e$5d1dc1c0$0c0101c0@saturne

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: o.haro@en-compro.com
  Subject: Re: General performance problem!
  In-Reply-To: <002901c4845e$5d1dc1c0$0c0101c0@saturne>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox