Home > mailing lists

Thread: General performance problem!

General performance problem!

From

"olivier HARO"

Date:

17 August 2004, 13:30:36

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

Re: General performance problem!

From

"Leeuw van der, Tim"

Date:

17 August 2004, 13:57:39

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

Re: General performance problem!

From

Gaetano Mendola

Date:

17 August 2004, 14:12:34

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

Re: General performance problem!

From

Richard Huxton

Date:

17 August 2004, 14:16:17

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

Re: General performance problem!

From

"olivier HARO"

Date:

17 August 2004, 15:41:42

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

Re: General performance problem!

From

Duane Lee - EGOVX

Date:

17 August 2004, 16:59:16

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 :

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