Re: Sun vs a P2. Interesting results.

I'm still seeing differences in the planner estimates, have you run a VACUUM
ANALYZE prior to running these tests?

Also, are the disk subsystems in these 2 systems the same? You may be seeing
some discrepancies in things spindle speed,  U160 vs U320, throughput on
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='m'
>        and  country_v and country = 'br'
>        and dob_v
>        and dob    >= '2003-08-26'::timestamptz - '29
> years'::interval
>        and dob <= '2003-08-26'::timestamptz - '18 years'::interval
> order by dob asc
> limit 20
> offset 100
>
> Explain Analyze's: (basically the same)
> Sun:
>  Limit  (cost=2390.05..2390.10 rows=20 width=67) (actual
> time=1098.34..1098.39 rows=20 loops=1)
>    ->  Sort  (cost=2389.80..2390.24 rows=178 width=67) (actual
> time=1098.16..1098.28 rows=121 loops=1)
>          Sort Key: dob
>          ->  Seq Scan on imuserprofile  (cost=0.00..2383.14 rows=178
> width=67) (actual time=0.38..1068.94 rows=1783 loops=1)
>                Filter: (gender_v AND (gender = 'm'::character varying) AND
> country_v AND (country = 'br'::character varying) AND dob_v AND (dob >=
> '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=
> '1985-08-26 00:00:00-04'::timestamp with time zone))
>  Total runtime: 1099.93 msec
> (6 rows)
>
>
> p2
>
>  Limit  (cost=2353.38..2353.43 rows=20 width=67) (actual
> time=371.75..371.83 rows=20 loops=1)
>    ->  Sort  (cost=2353.13..2353.60 rows=186 width=67) (actual
> time=371.46..371.63 rows=121 loops=1)
>          Sort Key: dob
>          ->  Seq Scan on imuserprofile  (cost=0.00..2346.14 rows=186
> width=67) (actual time=0.17..345.53 rows=1783 loops=1)
>                Filter: (gender_v AND (gender = 'm'::character varying) AND
> country_v AND (country = 'br'::character varying) AND dob_v AND (dob >=
> '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <=
> '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.

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com