Re: 7.3 vs 7.4 performance

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)