speed of querry? - Mailing list pgsql-performance

I must be missing something important, because I am just not seeing why this
query is slower on a 4 processor 8 gig machine running redhat AS4.

The SQL:
explain analyze SELECT a.clientnum, a.associateid, a.associatenum,
a.lastname, a.firstname, jt.value AS jobtitle, l.name AS "location",
l.locationid AS mainlocationid, l.divisionid, l.regionid, l.districtid,
(a.lastname::text || ', '::text) || a.firstname::text AS assocname,
a.isactive, a.isdeleted
   FROM tblassociate a
   left JOIN tbljobtitle jt ON a.jobtitleid = jt.id AND jt.clientnum::text =
a.clientnum::text AND 1 = jt.presentationid
   JOIN tbllocation l ON a.locationid = l.locationid AND l.clientnum::text =
a.clientnum::text
where a.clientnum = 'SAKS';

Machine 1 my desktop:
"Merge Join  (cost=74970.51..75975.46 rows=8244 width=113) (actual
time=5141.000..6363.000 rows=160593 loops=1)"
"  Merge Cond: ("outer".locationid = "inner".locationid)"
"  ->  Sort  (cost=656.22..657.11 rows=354 width=49) (actual
time=16.000..16.000 rows=441 loops=1)"
"        Sort Key: l.locationid"
"        ->  Index Scan using ix_location on tbllocation l
(cost=0.00..641.23 rows=354 width=49) (actual time=0.000..0.000 rows=441
loops=1)"
"              Index Cond: ('SAKS'::text = (clientnum)::text)"
"  ->  Sort  (cost=74314.29..74791.06 rows=190710 width=75) (actual
time=5125.000..5316.000 rows=160594 loops=1)"
"        Sort Key: a.locationid"
"        ->  Merge Right Join  (cost=0.00..52366.50 rows=190710 width=75)
(actual time=16.000..1973.000 rows=177041 loops=1)"
"              Merge Cond: ((("outer".clientnum)::text =
("inner".clientnum)::text) AND ("outer".id = "inner".jobtitleid))"
"              ->  Index Scan using ix_tbljobtitle_id on tbljobtitle jt
(cost=0.00..244.75 rows=6622 width=37) (actual time=0.000..16.000 rows=5690
loops=1)"
"                    Filter: (1 = presentationid)"
"              ->  Index Scan using ix_tblassoc_jobtitleid on tblassociate a
(cost=0.00..50523.83 rows=190710 width=53) (actual time=0.000..643.000
rows=177041 loops=1)"
"                    Index Cond: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 6719.000 ms"

Test Linux machine:
"Merge Join  (cost=48126.04..49173.57 rows=15409 width=113) (actual
time=11832.165..12678.025 rows=160593 loops=1)"
"  Merge Cond: ("outer".locationid = "inner".locationid)"
"  ->  Sort  (cost=807.64..808.75 rows=443 width=49) (actual
time=2.418..2.692 rows=441 loops=1)"
"        Sort Key: l.locationid"
"        ->  Index Scan using ix_location on tbllocation l
(cost=0.00..788.17 rows=443 width=49) (actual time=0.036..1.677 rows=441
loops=1)"
"              Index Cond: ('SAKS'::text = (clientnum)::text)"
"  ->  Sort  (cost=47318.40..47758.44 rows=176015 width=75) (actual
time=11829.660..12002.746 rows=160594 loops=1)"
"        Sort Key: a.locationid"
"        ->  Merge Right Join  (cost=24825.80..27512.71 rows=176015
width=75) (actual time=8743.848..9750.775 rows=177041 loops=1)"
"              Merge Cond: ((("outer".clientnum)::text =
"inner"."?column10?") AND ("outer".id = "inner".jobtitleid))"
"              ->  Index Scan using ix_tbljobtitle_id on tbljobtitle jt
(cost=0.00..239.76 rows=6604 width=37) (actual time=0.016..11.323 rows=5690
loops=1)"
"                    Filter: (1 = presentationid)"
"              ->  Sort  (cost=24825.80..25265.84 rows=176015 width=53)
(actual time=8729.320..8945.292 rows=177041 loops=1)"
"                    Sort Key: (a.clientnum)::text, a.jobtitleid"
"                    ->  Index Scan using ix_associate_clientnum on
tblassociate a  (cost=0.00..9490.20 rows=176015 width=53) (actual
time=0.036..1071.867 rows=177041 loops=1)"
"                          Index Cond: ((clientnum)::text = 'SAKS'::text)"
"Total runtime: 12802.019 ms"

I tried to remove the left outer thinking it would speed it up, and it used
a seq search on tblassoc and ran 2 times slower.


Joel Fradkin

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305

jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.