Re: Sun vs a P2. Interesting results.

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.

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/