pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Orion Henry <orion@trustcommerce.com>
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3 vs 7.4 performance
Date: 06 Feb 2004 17:49:05 -0800
Message-ID: <1076118545.3377.164.camel@orthanc> (raw)
In-Reply-To: <200402042127.59134.josh@agliodbs.com>
References: <1075958197.2438.330.camel@orthanc>
	<200402042127.59134.josh@agliodbs.com>

On Wed, 2004-02-04 at 21:27, Josh Berkus wrote:

> Orion,
> 
> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> > slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> > my query/data selection?
> 
> No, it's not common knowledge.  It should be the other way around.   Perhaps 
> it's the queries you picked?   Even so .....  feel free to post individual 
> EXPLAIN ANALYZEs to the list.


Thank you...

Here's one good example of 7.3 beating 7.4 soundly:
Again this could me some compile option since I built the 7.4 RPM 
from source and I got the 7.3 from Fedora or something to
do with the Opteron architecture.  (Yes the compiled postgres
is 64 bit)

SELECT cid,media_name,media_type,count(*) as count,sum(a_amount) 
as a,sum(case when b_amount > 0 then b_amount else 0 end) as b,
sum(case when b_amount < 0 then b_amount else 0 end) as c 
FROM transdata JOIN media_info ON (media = media_type) 
WHERE cid = 140100 AND demo is not null 
AND trans_date between date '2004-01-01' 
AND date_trunc('month',date '2004-01-01' + interval '32 days') 
GROUP BY cid,media_name,media_type;

Here's 7.3's time and explain

real    0m34.260s
user    0m0.010s
sys     0m0.000s

---------------------------------------------------------------
 Aggregate  (cost=7411.88..7415.32 rows=17 width=25)
   ->  Group  (cost=7411.88..7413.60 rows=172 width=25)
         ->  Sort  (cost=7411.88..7412.31 rows=172 width=25)
               Sort Key: transdata.cid, media_info.media_name, transdata.media_type
               ->  Hash Join  (cost=1.22..7405.50 rows=172 width=25)
                     Hash Cond: ("outer".media_type = "inner".media)
                     ->  Index Scan using transdata_date_index on transdata  (cost=0.00..7401.27 rows=172 width=14)
                           Index Cond: ((trans_date >= ('2004-01-01'::date)::timestamp with time zone) AND (trans_date <= ('2004-02-01 00:00:00'::timestamp without time zone)::timestamp with time zone))
                           Filter: ((cid = 140100) AND (demo IS NOT NULL))
                     ->  Hash  (cost=1.18..1.18 rows=18 width=11)
                           ->  Seq Scan on media_info  (cost=0.00..1.18 rows=18 width=11)


Here's 7.4's time and explain

real    0m43.052s
user    0m0.000s
sys     0m0.020s

                                                                                          QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=8098.26..8098.29 rows=2 width=23)
   ->  Hash Join  (cost=1.22..8095.48 rows=159 width=23)
         Hash Cond: ("outer".media_type = "inner".media)
         ->  Index Scan using transdata_date_index on transdata  (cost=0.00..8091.87 rows=159 width=14)
               Index Cond: ((trans_date >= ('2004-01-01'::date)::timestamp with time zone) AND (trans_date <= ('2004-02-01 00:00:00'::timestamp without time zone)::timestamp with time zone))
               Filter: ((cid = 140100) AND (demo IS NOT NULL))
         ->  Hash  (cost=1.18..1.18 rows=18 width=11)
               ->  Seq Scan on media_info  (cost=0.00..1.18 rows=18 width=11)

Attachments:

  signature.asc (application/pgp-signature, 188B) download

view thread (16+ messages)  latest in thread

Message-ID: <1076118545.3377.164.camel@orthanc>
Permalink:  ../1076118545.3377.164.camel@orthanc/
Also on:    postgresql.org/message-id/1076118545.3377.164.camel@orthanc

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: orion@trustcommerce.com, josh@agliodbs.com
  Subject: Re: 7.3 vs 7.4 performance
  In-Reply-To: <1076118545.3377.164.camel@orthanc>

* 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