pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneral performance problem!
6+ messages / 5 participants
[nested] [flat]
* General performance problem!
@ 2004-08-17 13:30 olivier HARO <o.haro@en-compro.com>
2004-08-17 13:48 ` Re: General performance problem! Gaetano Mendola <mendola@bigfoot.com>
2004-08-17 14:16 ` Re: General performance problem! Richard Huxton <dev@archonet.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:30 General performance problem! olivier HARO <o.haro@en-compro.com>
@ 2004-08-17 13:48 ` Gaetano Mendola <mendola@bigfoot.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:30 General performance problem! olivier HARO <o.haro@en-compro.com>
@ 2004-08-17 14:16 ` Richard Huxton <dev@archonet.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 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 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