Re: performance question

> On Mon, 20 Jan 2003 12:40:34 +0100 (CET), "Moritz Lennert"
> <mlennert(at)club(dot)worldonline(dot)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