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