X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 81699D1D30E for ; Sat, 7 Feb 2004 01:49:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84229-09 for ; Fri, 6 Feb 2004 21:49:19 -0400 (AST) Received: from front1.mail.megapathdsl.net (front1.mail.megapathdsl.net [66.80.60.31]) by svr1.postgresql.org (Postfix) with ESMTP id 33C7BD1D2AD for ; Fri, 6 Feb 2004 21:49:15 -0400 (AST) Received: from [64.32.131.193] (HELO [192.168.1.11]) by front1.mail.megapathdsl.net (CommuniGate Pro SMTP 4.1.3) with ESMTP id 144553296; Fri, 06 Feb 2004 17:49:09 -0800 Subject: Re: 7.3 vs 7.4 performance From: Orion Henry To: josh@agliodbs.com Cc: pgsql-performance@postgresql.org In-Reply-To: <200402042127.59134.josh@agliodbs.com> References: <1075958197.2438.330.camel@orthanc> <200402042127.59134.josh@agliodbs.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-atji1b62IFzCT1nJfa2W" Organization: Message-Id: <1076118545.3377.164.camel@orthanc> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 06 Feb 2004 17:49:05 -0800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/106 X-Sequence-Number: 5630 --=-atji1b62IFzCT1nJfa2W Content-Type: multipart/alternative; boundary="=-aPwhXs4ml9ImxRnprlHw" --=-aPwhXs4ml9ImxRnprlHw Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Wed, 2004-02-04 at 21:27, Josh Berkus wrote: > Orion, >=20 > > 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? >=20 > No, it's not common knowledge. It should be the other way around. Perh= aps=20 > it's the queries you picked? Even so ..... feel free to post individua= l=20 > 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=20 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)=20 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=20 FROM transdata JOIN media_info ON (media =3D media_type)=20 WHERE cid =3D 140100 AND demo is not null=20 AND trans_date between date '2004-01-01'=20 AND date_trunc('month',date '2004-01-01' + interval '32 days')=20 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=3D7411.88..7415.32 rows=3D17 width=3D25) -> Group (cost=3D7411.88..7413.60 rows=3D172 width=3D25) -> Sort (cost=3D7411.88..7412.31 rows=3D172 width=3D25) Sort Key: transdata.cid, media_info.media_name, transdata.me= dia_type -> Hash Join (cost=3D1.22..7405.50 rows=3D172 width=3D25) Hash Cond: ("outer".media_type =3D "inner".media) -> Index Scan using transdata_date_index on transdata= (cost=3D0.00..7401.27 rows=3D172 width=3D14) Index Cond: ((trans_date >=3D ('2004-01-01'::dat= e)::timestamp with time zone) AND (trans_date <=3D ('2004-02-01 00:00:00'::= timestamp without time zone)::timestamp with time zone)) Filter: ((cid =3D 140100) AND (demo IS NOT NULL)) -> Hash (cost=3D1.18..1.18 rows=3D18 width=3D11) -> Seq Scan on media_info (cost=3D0.00..1.18 r= ows=3D18 width=3D11) Here's 7.4's time and explain real 0m43.052s user 0m0.000s sys 0m0.020s = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------- HashAggregate (cost=3D8098.26..8098.29 rows=3D2 width=3D23) -> Hash Join (cost=3D1.22..8095.48 rows=3D159 width=3D23) Hash Cond: ("outer".media_type =3D "inner".media) -> Index Scan using transdata_date_index on transdata (cost=3D0.= 00..8091.87 rows=3D159 width=3D14) Index Cond: ((trans_date >=3D ('2004-01-01'::date)::timestam= p with time zone) AND (trans_date <=3D ('2004-02-01 00:00:00'::timestamp wi= thout time zone)::timestamp with time zone)) Filter: ((cid =3D 140100) AND (demo IS NOT NULL)) -> Hash (cost=3D1.18..1.18 rows=3D18 width=3D11) -> Seq Scan on media_info (cost=3D0.00..1.18 rows=3D18 wid= th=3D11) --=-aPwhXs4ml9ImxRnprlHw Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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.   Perhap=
s=20
it's the queries you picked?   Even so .....  feel free to post individual=
=20
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=20
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)=20
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=20
FROM transdata JOIN media_info ON (media =3D media_type)=20
WHERE cid =3D 140100 AND demo is not null=20
AND trans_date between date '2004-01-01'=20
AND date_trunc('month',date '2004-01-01' + interval '32 days')=20
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=3D7411.88..7415.32 rows=3D17 width=3D25)
   ->  Group  (cost=3D7411.88..7413.60 rows=3D172 wi=
dth=3D25)
         ->  Sort  (co=
st=3D7411.88..7412.31 rows=3D172 width=3D25)
            &nb=
sp;  Sort Key: transdata.cid, media_info.media_name, transdata.media_t=
ype
            &nb=
sp;  ->  Hash Join  (cost=3D1.22..7405.50 rows=3D172 widt=
h=3D25)
            &nb=
sp;        Hash Cond: ("outer"=
.media_type =3D "inner".media)
            &nb=
sp;        ->  Index Scan using =
transdata_date_index on transdata  (cost=3D0.00..7401.27 rows=3D172 wi=
dth=3D14)
            &nb=
sp;            =
  Index Cond: ((trans_date >=3D ('2004-01-01'::date)::timestamp wit=
h time zone) AND (trans_date <=3D ('2004-02-01 00:00:00'::timestamp with=
out time zone)::timestamp with time zone))
            &nb=
sp;            =
  Filter: ((cid =3D 140100) AND (demo IS NOT NULL))
            &nb=
sp;        ->  Hash  (cost=
=3D1.18..1.18 rows=3D18 width=3D11)
            &nb=
sp;            =
  ->  Seq Scan on media_info  (cost=3D0.00..1.18 rows=3D1=
8 width=3D11)


Here's 7.4's time and explain

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

            &nb=
sp;            =
            &nb=
sp;            =
            &nb=
sp;            =
            &nb=
sp;  QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------
 HashAggregate  (cost=3D8098.26..8098.29 rows=3D2 width=3D23)
   ->  Hash Join  (cost=3D1.22..8095.48 rows=3D159 w=
idth=3D23)
         Hash Cond: ("outer&qu=
ot;.media_type =3D "inner".media)
         ->  Index Scan usi=
ng transdata_date_index on transdata  (cost=3D0.00..8091.87 rows=3D159=
 width=3D14)
            &nb=
sp;  Index Cond: ((trans_date >=3D ('2004-01-01'::date)::timestamp =
with time zone) AND (trans_date <=3D ('2004-02-01 00:00:00'::timestamp w=
ithout time zone)::timestamp with time zone))
            &nb=
sp;  Filter: ((cid =3D 140100) AND (demo IS NOT NULL))
         ->  Hash  (co=
st=3D1.18..1.18 rows=3D18 width=3D11)
            &nb=
sp;  ->  Seq Scan on media_info  (cost=3D0.00..1.18 rows=
=3D18 width=3D11)



--=-aPwhXs4ml9ImxRnprlHw-- --=-atji1b62IFzCT1nJfa2W Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQBAJEQR9IoQcq23xtARAuPiAKChkleDgV159Tw7weSKZTXOSWp8BgCfbtqb M59Z9FiGJIoDCdAZsYUYC+k= =/Pn/ -----END PGP SIGNATURE----- --=-atji1b62IFzCT1nJfa2W--