X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 59967D1B8C3 for ; Tue, 26 Aug 2003 16:02:26 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 01238-04 for ; Tue, 26 Aug 2003 16:02:16 -0300 (ADT) Received: from mail2.dbitech.ca (radius.wavefire.com [64.141.13.252]) by svr1.postgresql.org (Postfix) with SMTP id CA3F3D1B8A5 for ; Tue, 26 Aug 2003 16:02:13 -0300 (ADT) Received: (qmail 20326 invoked from network); 26 Aug 2003 19:24:05 -0000 Received: from dbitech.wavefire.com (HELO dbitech) (darcy@64.141.15.253) by radius.wavefire.com with SMTP; 26 Aug 2003 19:24:05 -0000 Content-Type: text/plain; charset="iso-8859-1" From: Darcy Buskermolen Organization: Wavefire Technologies Corp. To: Jeff Subject: Re: Sun vs a P2. Interesting results. Date: Tue, 26 Aug 2003 12:01:55 -0700 User-Agent: KMail/1.4.3 Cc: "pgsql-performance@postgresql.org" References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200308261201.55733.darcy@wavefire.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/343 X-Sequence-Number: 3159 I'm still seeing differences in the planner estimates, have you run a VACUU= M=20 ANALYZE prior to running these tests? Also, are the disk subsystems in these 2 systems the same? You may be seein= g=20 some discrepancies in things spindle speed, U160 vs U320, throughput on=20 specific RAID controlers, different blocksize, ect. On Tuesday 26 August 2003 11:41, Jeff wrote: > On Tue, 26 Aug 2003, Darcy Buskermolen wrote: > > Also, after having taken another look at this, you aren't preforming the > > same query on both datasets, so you can't expect them to generate the > > same results, or the same query plans, or even comparable times. Please > > retry your tests with identical queries , specify the dates, don;t use a > > function like now() to retrieve them. > > Given what you said in the previous email and this one here's some new > information. I redid the query to use a static starting time and I ran > 19 beaters in parallel. After I send this mail out I'll try it with 40. > > New Query: > > select userkey, dob, email, gender, country from userprofile > where gender_v and gender=3D'm' > and country_v and country =3D 'br' > and dob_v > and dob >=3D '2003-08-26'::timestamptz - '29 > years'::interval > and dob <=3D '2003-08-26'::timestamptz - '18 years'::interval > order by dob asc > limit 20 > offset 100 > > Explain Analyze's: (basically the same) > Sun: > Limit (cost=3D2390.05..2390.10 rows=3D20 width=3D67) (actual > time=3D1098.34..1098.39 rows=3D20 loops=3D1) > -> Sort (cost=3D2389.80..2390.24 rows=3D178 width=3D67) (actual > time=3D1098.16..1098.28 rows=3D121 loops=3D1) > Sort Key: dob > -> Seq Scan on imuserprofile (cost=3D0.00..2383.14 rows=3D178 > width=3D67) (actual time=3D0.38..1068.94 rows=3D1783 loops=3D1) > Filter: (gender_v AND (gender =3D 'm'::character varying) = AND > country_v AND (country =3D 'br'::character varying) AND dob_v AND (dob >= =3D > '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=3D > '1985-08-26 00:00:00-04'::timestamp with time zone)) > Total runtime: 1099.93 msec > (6 rows) > > > p2 > > Limit (cost=3D2353.38..2353.43 rows=3D20 width=3D67) (actual > time=3D371.75..371.83 rows=3D20 loops=3D1) > -> Sort (cost=3D2353.13..2353.60 rows=3D186 width=3D67) (actual > time=3D371.46..371.63 rows=3D121 loops=3D1) > Sort Key: dob > -> Seq Scan on imuserprofile (cost=3D0.00..2346.14 rows=3D186 > width=3D67) (actual time=3D0.17..345.53 rows=3D1783 loops=3D1) > Filter: (gender_v AND (gender =3D 'm'::character varying) = AND > country_v AND (country =3D 'br'::character varying) AND dob_v AND (dob >= =3D > '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=3D > '1985-08-26 00:00:00-04'::timestamp with time zone)) > Total runtime: 372.63 msec > (6 rows) > > > I ran this query 100 times per beater (no prepared queries) and ran > 19 beaters in parellel. > > P2 Machine: 345sec avg > Sun: 565sec avg > > > > I know solaris/sun isn't the preferred pg platform, and we have plenty of > capicty even with these numbers, I just find it a little suprising the > speed difference. --=20 Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com