pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance tuning in PostgreSQL?
2+ messages / 2 participants
[nested] [flat]

* Performance tuning in PostgreSQL?
@ 2001-07-10 12:17 Raymond Chui <raymond.chui@noaa.gov>
  0 siblings, 0 replies; 2+ messages in thread

From: Raymond Chui @ 2001-07-10 12:17 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-sql



Hi dear dudes (buddies)!

I have have a table with 8 columns, the primary key are column ID and
orders.

CREATE TABLE tablename (
ID char(8) not null,
orders integer not null,
col3 float(6) DEFAULT 0.01,
col4 integer DEFAULT 15,
col5 float(6) DEFAULT 0.0,
col6 char(5) not null,
col7 char(2) not null,
col8 timestamp DEFAULT 1970-01-01 00:00:00,
primary key (ID, orders),
check (orders >= 0));

CREATE INDEX indexname ON tablename (ID, orders);

There are more than 25 thousand rows in the table. This is a relative
small database compare to Wal-Mark, Macy (big retail stores) inventory.
But I have some performance issue. When I randomly query the table

SELECT columnlist FROM tablename
WHERE ID = '12345678' ORDER BY orders;

It sometime take few seconds to display output query, sometime display
output query instantly. This tell me when the ID I query is in the cache
memory,
I got output results instantly, when the ID is not in cache memory, I
have to wait
for few seconds. Because when I randomly pick an ID for above SELECT
statement, 1st time I need to wait for few seconds, if I do the same
SELECT
again, I got the query output immediately/instantly. To wait for few
seconds
is not a big deal for most of application. If you doing online shopping,
wait for
few seconds is very fast. But for our application is close to the
real-time application. We can't have any wait, must no waiting at all
time.

In other database systems, such as Informix, Sybase, etc. The database
is stored
in the raw disk partitions, you can config the database system into
different
partitions, different disks, slice into different trunks, etc. But
PostgreSQL is
stored the database in the file system in PGDATA directory.

So how do I boost the performance in PostgreSQL?
Thank you very much in advance!


--Raymond

Attachments:

raymond.chui.vcf (text/x-vcard, 413B) download begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:Raymond.Chui@noaa.gov title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard
^ permalink raw reply [nested|flat] 2+ messages in thread

* Re: Performance tuning in PostgreSQL?
@ 2001-07-17 11:56 Edipo Elder Fernandes de Melo <edipoelder@ig.com.br>
  0 siblings, 0 replies; 2+ messages in thread

From: Edipo Elder Fernandes de Melo @ 2001-07-17 11:56 UTC (permalink / raw)
  To: pgsql-sql

Em 10 Jul 2001, Raymond Chui escreveu:
 

 
>In other database systems, such as Informix, Sybase, etc. The database 
 
>is stored 
 
>in the raw disk partitions, you can config the database system into 
 
>different 
 
>partitions, different disks, slice into different trunks, etc. But 
 
>PostgreSQL is 
 
>stored the database in the file system in PGDATA directory. 
 

 
    ...and how to use two processors in a query (if it's possible)? We have 
a new server with two CPU and, in a query, Postgres uses only one of them.
 

 
    Thanks,
 

 
    Edipo Elder
 
    [edipoelder@ig.com.br]
 

_________________________________________________________
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://registro.ig.com.br/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-07-17 11:56 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-10 12:17 Performance tuning in PostgreSQL? Raymond Chui <raymond.chui@noaa.gov>
2001-07-17 11:56 Re: Performance tuning in PostgreSQL? Edipo Elder Fernandes de Melo <edipoelder@ig.com.br>

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