From: Moritz Lennert <mlennert@club.worldonline.be>
To: pgsql-sql@postgresql.org
Subject: Re: performance question
Date: Mon, 20 Jan 2003 17:45:12 +0100 (CET)
Message-ID: <36464.164.15.128.4.1043081112.squirrel@http://moritz.homelinux.org> (raw)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlennert@club.worldonline.be
Subject: Re: performance question
In-Reply-To: <36464.164.15.128.4.1043081112.squirrel@http://moritz.homelinux.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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