pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
To: pgsql-performance@postgresql.org
Subject: Same query, same performance
Date: Thu, 23 Jan 2003 00:26:44 -0200 (BRST)
Message-ID: <10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br> (raw)

Hi all,

First, sorry for the long mail...

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

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

My question is:

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 ???

The variations of query show bellow have the times pratically
equals and my system send thousands os this querys with a
thousands of 1.18 seconds... :(

Very thank“s

Alexandre


Query:
[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
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=25875.53..25875.53 rows=15 width=155)
   Sort Key: t1.fn06emp07, t1.fn06tiptit, t1.fn06numtit, t1.fn06desdob,
t1.fn05codpre, t1.fn06ecli1, t1.fn06tcli1, t1.fn06ccli1
   ->  Nested Loop  (cost=0.00..25875.50 rows=15 width=155)
         ->  Seq Scan on fn06t t1  (cost=0.00..25808.30 rows=15 width=95)
               Filter: ((fn06emp07 = 1::smallint) AND (fn06tiptit =
'R'::bpchar) AND (fn06titban = '002021001525               
  '::bpchar))
         ->  Index Scan using fi15t_pkey on fi15t t2  (cost=0.00..4.33
rows=1 width=60)
               Index Cond: ((t2.fi15emp05 = "outer".fn06ecli1) AND
(t2.fi15flagcf = "outer".fn06tcli1) AND (t2.fi15codcf =
"outer".fn06ccli1))
(7 rows)

*** AND FROM LOG when a execute the query:

2003-01-23 00:09:49 [3372]   LOG:  duration: 1.285900 sec
2003-01-23 00:09:49 [3372]   LOG:  QUERY STATISTICS
! system usage stats:
!       1.286001 elapsed 1.240000 user 0.040000 system sec
!       [1.250000 user 0.040000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       50526/130 [50693/372] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [0/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit
rate = 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit
rate = 0.00%
!       Direct blocks:          0 read,          0 written






view thread (5+ messages)  latest in thread

Message-ID: <10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br>
Permalink:  ../10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br/
Also on:    postgresql.org/message-id/10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br

 · 

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: alepaes@aldeiadigital.com.br
  Subject: Re: Same query, same performance
  In-Reply-To: <10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br>
  References: <10635.200.225.202.15.1043288804.squirrel@webmail.ad2.com.br>

* 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