pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
General performance problem!
6+ messages / 5 participants
[nested] [flat]

* General performance problem!
@ 2004-08-17 13:30  olivier HARO <o.haro@en-compro.com>
  0 siblings, 2 replies; 6+ messages in thread

From: olivier HARO @ 2004-08-17 13:30 UTC (permalink / raw)
  To: pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: General performance problem!
@ 2004-08-17 13:48  Gaetano Mendola <mendola@bigfoot.com>
  parent: olivier HARO <o.haro@en-compro.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Gaetano Mendola @ 2004-08-17 13:48 UTC (permalink / raw)
  To: olivier HARO <o.haro@en-compro.com>

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












^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: General performance problem!
@ 2004-08-17 13:57  Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Leeuw van der, Tim @ 2004-08-17 13:57 UTC (permalink / raw)
  To: olivier HARO <o.haro@en-compro.com>; pgsql-performance

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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of olivier HARO
Sent: dinsdag 17 augustus 2004 15:30
To: pgsql-performance@postgresql.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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: General performance problem!
@ 2004-08-17 14:16  Richard Huxton <dev@archonet.com>
  parent: olivier HARO <o.haro@en-compro.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Richard Huxton @ 2004-08-17 14:16 UTC (permalink / raw)
  To: olivier HARO <o.haro@en-compro.com>; +Cc: pgsql-performance

olivier HARO wrote:
> 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

We'll need to know what version of PostgreSQL you're using and also what 
the output of EXPLAIN ANALYZE shows for your example queries.

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: General performance problem!
@ 2004-08-17 15:41  olivier HARO <o.haro@en-compro.com>
  0 siblings, 0 replies; 6+ messages in thread

From: olivier HARO @ 2004-08-17 15:41 UTC (permalink / raw)
  To: pgsql-performance

Thanks for the tip for the index on multiple columns !  (I never do inserts
on this table so insert time doesn't matter)

Mys posgresql version is : PostgreSQL 7.2.1

Here are the results of the EXPLAIN ANALYZE you asked me to execute.


explain analyse select cp from base_aveugle where cp='69740' and effectif
between 1 and 50;
   NOTICE:  QUERY PLAN:
      Index Scan using base_aveugle_cp_eff on base_aveugle
(cost=0.00..508.69 rows=126 width=32)
      (actual time=0.27..11.56 rows=398 loops=1)
      Total runtime: 11.77 msec
   EXPLAIN

explain analyse select cp from base_aveugle where cp like '69%' and effectif
between 1 and 50 and naf like '24%' or naf like '25%';
   NOTICE:  QUERY PLAN:
   Index Scan using base_aveugle_cp_eff_naf, base_aveugle_naf on
base_aveugle  (cost=0.00..100001.89 rows=25245 width=32)
   (actual time=4.40..353.69 rows=6905 loops=1)
   Total runtime: 355.82 msec
   EXPLAIN


thx ;)


---
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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: General performance problem!
@ 2004-08-17 16:58  Duane Lee - EGOVX <DLee@mail.maricopa.gov>
  0 siblings, 0 replies; 6+ messages in thread

From: Duane Lee - EGOVX @ 2004-08-17 16:58 UTC (permalink / raw)
  To: 'olivier HARO' <o.haro@en-compro.com>; pgsql-performance

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:o.haro@en-compro.com]
Sent: Tuesday, August 17, 2004 6:30 AM
To: pgsql-performance@postgresql.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
<http://www.grisoft.com; ).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11/08/2004

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-08-17 16:58 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 13:30 General performance problem! olivier HARO <o.haro@en-compro.com>
2004-08-17 13:48 ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-17 14:16 ` Richard Huxton <dev@archonet.com>
2004-08-17 13:57 Re: General performance problem! Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2004-08-17 15:41 Re: General performance problem! olivier HARO <o.haro@en-compro.com>
2004-08-17 16:58 Re: General performance problem! Duane Lee - EGOVX <DLee@mail.maricopa.gov>

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