Skip site navigation (1) Skip section navigation (2)

Performance in tables to large

From: "Luz Lopez" <viaris(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Performance in tables to large
Date: 2000-09-27 15:17:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
I have a table with 3,000,000 of rows, I have the following indexs:

create index regist_idx on regist(pname,sname,plastname,slastname);
create index regist_idx1 on regist(pname,plastname,slastname);
create index regist_idx2 on regist(ci);
create index regist_idx3 on regist(plastname);
create index regist_idx4 on regist(pname);
create index regist_idx5 on regist(pname,plastname);
create index regist_idx6 on regist(pname,slastname);
create index regist_idx7 on regist(sname,plastname,slastname);
create index regist_idx8 on regist(sname,slastname);
create index regist_idx9 on regist(sname);
create index regist_idx10 on regist(slastname);

if the query is on 4 columns of the idx1, the result is speed, but if teh
query is on  two columns of the idx1 the rsult take 10 minutes.

My postmaster run with option -S -o -F for best performance, but the
results is very slow.

Teh machine have: 256 MB memory, SWAP 512, System Operate: Red Hat Linux
6.2, posgresql version 6.5.3 the table struct is:

table regist(tipo varchar(1),cod_barr integer,ci varchar(13),jrv integer
not null,plastname varchar(15),slastname varchar(15),pname
varchar(15),sname varchar(15),lug_nac smallint,fec_nac varchar(10))

I already use vacuum analize. I ran explain with the select and the result 

Index Scan using regist_idx5 on regist (cost=25265.64 rows=6 width=94)

How can I have best performance?, Thanks in advance


Get Your Private, Free E-mail from MSN Hotmail at

Share information about yourself, create your own public profile at

pgsql-novice by date

Next:From: Tony SimopoulosDate: 2000-09-29 20:02:02
Subject: SQL*Loader?
Previous:From: Bo DahlstromDate: 2000-09-27 11:59:59
Subject: Re: Problem with initdb

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group