pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
To: pgsql-performance@postgresql.org
Subject: Re: Same query, same performance
Date: Thu, 23 Jan 2003 09:00:02 -0800
Message-ID: <web-2323224@davinci.ethosmedia.com> (raw)
In-Reply-To: <10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br>

Alexandre,

> I have a system with 7 Million of records in 600 tables.
> My actual production machine is: P4 1.6G, 3 IDE 7200, 1GB PC133
> My new machine production is: Dual Xeon 2.0G HT, 1GB DDR266 ECC
> 3 SCSI with HW Raid 5

Well, first of all, those two systems are almost equivalent as far as
Postgres is concerned for simple queries.   The extra processor power
will only help you with very complex queries.  3-disk RAID 5 is no
faster ... and sometimes slower ... than IDE for database purposes.
  The only real boost to the Xeon is the faster RAM ... which may not
help you if your drive array is the bottleneck.

> 
> The postgresql.conf is the SAME in both systems and I test
> with no other connections, only my local test.
> 
> shared_buffers = 80000
> effective_cache_size = 60000
> random_page_cost = 2.5
> cpu_tuple_cost = 0.001
> cpu_index_tuple_cost = 0.0001
> cpu_operator_cost = 0.00025

Not that it affects the query below, but what about SORT_MEM?

> If I execute the same query executed a lot of times, the
> duration is praticaly the same in both systems ?
> 
> 1) !       1.185424 elapsed 1.090000 user 0.100000 system sec
> 2) !       1.184415 elapsed 1.070000 user 0.120000 system sec
> 3) !       1.185209 elapsed 1.100000 user 0.080000 system sec
> 
> If the disks is not read directly, the system must find
> the rows in RAM. If it find in RAM, why so diffrents machines
> have the times of execution and why the times does not down ???

I'm pretty sure that PostgreSQL always checks on disk, even when the
same query is run repeatedly.  Tom?

> [postgres@host1 data]$ psql -c "explain SELECT T2.fi15emp05,
> T2.fi15flagcf, T2.fi15codcf, T1.Fn06Emp07, T1.Fn06TipTit,
> T1.Fn06TitBan,  
> T1.Fn06Conta1, T1.Fn06NumTit, T1.Fn06Desdob, T1.Fn05CodPre,
> T1.Fn06eCli1,
> T1.Fn06tCli1,   T1.Fn06cCli1, T2.fi15nome  FROM (FN06T T1 LEFT JOIN
> FI15T
> T2 ON T2.fi15emp05 = T1.Fn06eCli1   AND T2.fi15flagcf = T1.Fn06tCli1
> AND
> T2.fi15codcf = T1.Fn06cCli1) WHERE ( T1.Fn06Emp07   = '1' AND
> T1.Fn06TipTit = 'R' ) AND ( T1.Fn06TitBan = '002021001525
>                 
> ' ) ORDER BY T1.Fn06Emp07, T1.Fn06TipTit,   T1.Fn06NumTit,
> T1.Fn06Desdob,
> T1.Fn05CodPre, T1.Fn06eCli1, T1.Fn06tCli1, T1.Fn06cCli1" Pro13Z

Actually, from your stats, Postgres is doing a pretty good job.   1.18
seconds to return 15 rows from a 7 million row table searching on not
Indexed columns?  I don't think you have anything to complain about.

If you want less-than-1 second respose time: Add some indexes and keep
the tables VACUUMed so the indexes work.  Particularly, add a
multi-column index on ( T1.Fn06Emp07, T1.Fn06TipTit, T1.Fn06TitBan )

If you want single-digit-msec response: Get a better disk set for
Postgres: I recommend dual-channel RAID 1 (n addition to indexing).

-Josh Berkus





view thread (5+ messages)  latest in thread

Message-ID: <web-2323224@davinci.ethosmedia.com>
Permalink:  ../web-2323224@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-2323224@davinci.ethosmedia.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, alepaes@aldeiadigital.com.br
  Subject: Re: Same query, same performance
  In-Reply-To: <web-2323224@davinci.ethosmedia.com>
  References: <10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br> <web-2323224@davinci.ethosmedia.com>

* 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