Message-ID: 
 <36464.164.15.128.4.1043081112.squirrel@http://moritz.homelinux.org>
Date: Mon, 20 Jan 2003 17:45:12 +0100 (CET)
Subject: Re: performance question
From: "Moritz Lennert" <mlennert@club.worldonline.be>
To: pgsql-sql@postgresql.org
Reply-To: mlennert@club.worldonline.be
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Importance: Normal

> On Mon, 20 Jan 2003 12:40:34 +0100 (CET), "Moritz Lennert"
> <mlennert@club.worldonline.be> wrote:
>>I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
MB RAM.
>>Some queries I launch take quite a long time, and I'm wondering whether
this is normal,or whether I can get better performance somehow.
>
> Moritz, we need more information.  Please show us
>  . your PG version

 7.2.1-2woody2

>  . CREATE TABLE ...

CREATE TABLE "rec81" (
        "commune_residence" character(5),
        "sexe" character(1),
        "annee_naissance" smallint,
        "mois_naissance" smallint,
        "jour_naissance" smallint,
        "parent" character(2),
        "etat_civil" character(1),
        "nationalite" character(3),
        "type_menage" character(1),
        "logement_depuis_naiss" character(1),
        "domicile_mere" character(6),
        "dans_log_depuis_quand" smallint,
        "meme_log_1980" character(1),
        "commune_1980" character(6),
        "annee_entree_belgique" smallint,
        "age_fin_etude" smallint,
        "detenteur_diplome" character(1),
        "type_diplome" character(2),
        "detenteur_diplome_etranger" character(1),
        "actif" character(1),
        "actif_temporaire" character(1),
        "type_profession" character(4),
        "statut_professionnel" character(1),
        "temps_partiel" character(1),
        "nombre_heures_travail" smallint,
        "employeur" character(1),
        "nombre_personnes_ds_services" integer,
        "direction" character(1),
        "lieu_travail" character(6),
        "secteur_activite" character(3),
        "emploi_complementaire" character(1),
        "type_emploi_complementaire" character(4),
        "lieu_depart_navette" character(1),
        "commune_depart_navette" character(6),
        "distance" smallint,
        "nbre_navettes_par_jour" character(1),
        "nbre_jours_navette_par_semaine" character(1),
        "type_transport_navette" character(3),
        "duree_trajet_navette" character(1),
        "statut_non_occupe" character(2),
        "effectif_menage" smallint,
        "sec_stat_residence" character(6)
);

>  . indices

CREATE INDEX rec81_commune_residence_idx ON rec81 USING btree
(commune_residence);
CREATE INDEX rec81_annee_naissance_idx ON rec81 USING btree
(annee_naissance);
CREATE INDEX rec81_nationalite_idx ON rec81 USING btree (nationalite);
CREATE INDEX rec81_meme_log_1980_idx ON rec81 USING btree (meme_log_1980);
CREATE INDEX rec81_commune_1980_idx ON rec81 USING btree (commune_1980);
CREATE INDEX rec81_age_fin_etude_idx ON rec81 USING btree (age_fin_etude);
CREATE INDEX rec81_detenteur_diplome_idx ON rec81 USING btree
(detenteur_diplome);
CREATE INDEX rec81_type_profession_idx ON rec81 USING btree
(type_profession);
CREATE INDEX rec81_statut_professionnel_idx ON rec81 USING btree
(statut_professionnel);
CREATE INDEX rec81_lieu_travail_idx ON rec81 USING btree (lieu_travail);
CREATE INDEX rec81_secteur_activite_idx ON rec81 USING btree
(secteur_activite);
CREATE INDEX rec81_statut_non_occupe_idx ON rec81 USING btree
(statut_non_occupe);
CREATE INDEX rec81_sec_stat_residence_idx ON rec81 USING btree
(sec_stat_residence);
CREATE INDEX rec81_comres_typedipl_idx ON rec81 USING btree
commune_residence, type_diplome);
CREATE INDEX rec81_type_diplome_idx ON rec81 USING btree (type_diplome);

>  . your query

select commune_residence, type_diplome from rec81 where type_diplome = '11';

>  . EXPLAIN ANALYZE output

explain analyze select commune_residence, type_diplome from rec81 where
type_diplome = '11';
NOTICE:  QUERY PLAN:

Seq Scan on rec81  (cost=0.00..120316.30 rows=177698 width=15) (actual
time=23.03..219164.82 rows=176621 loops=1)
Total runtime: 226149.03 msec

EXPLAIN

>  . your settings, especially shared_buffers, sort_mem,
> random_page_cost, effective_cache_size


shared_buffers = 128

The others are not set (I suppose they should be set in the
postgresql.conf file ?)


Thanks for your help !

Moritz