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

Search Discussions

  • Leeuw van der, Tim at Aug 17, 2004 at 1:57 pm
    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 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
  • Gaetano Mendola at Aug 17, 2004 at 2:12 pm

    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
  • Richard Huxton at Aug 17, 2004 at 2:16 pm

    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
  • olivier HARO at Aug 17, 2004 at 3:41 pm
    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
  • Duane Lee - EGOVX at Aug 17, 2004 at 4:59 pm
    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
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedAug 17, '04 at 1:30p
activeAug 17, '04 at 4:59p
posts6
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase