Re: 7.3 vs 7.4 performance - Mailing list pgsql-performance

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)