X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 90FC78B9B58 for ; Sat, 5 Feb 2005 21:53:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50272-10 for ; Sat, 5 Feb 2005 21:53:03 +0000 (GMT) Received: from out005.verizon.net (out005pub.verizon.net [206.46.170.143]) by svr1.postgresql.org (Postfix) with ESMTP id 553AD8B9C6C for ; Sat, 5 Feb 2005 21:52:40 +0000 (GMT) Received: from jfradkin ([66.15.127.235]) by out005.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id <20050205213433.WTYQ25302.out005.verizon.net@jfradkin> for ; Sat, 5 Feb 2005 15:34:33 -0600 From: "Joel Fradkin" To: Subject: Assoclist is still slow and I am not seeing it Date: Sat, 5 Feb 2005 16:38:00 -0500 Message-ID: <000001c50bca$f7cc5530$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C50BA1.0EF64D30" X-Authentication-Info: Submitted using SMTP AUTH at out005.verizon.net from [66.15.127.235] at Sat, 5 Feb 2005 15:34:33 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=4.158 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, MSGID_DOLLARS, WEIRD_QUOTING X-Spam-Level: **** X-Archive-Number: 200502/93 X-Sequence-Number: 20204 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C50BA1.0EF64D30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, I asked about this before and am still fighting for success; any ideas I tried adding a index on jobtitle. Explain analyze returns QUERY PLAN "Merge Join (cost=53053.05..53652.66 rows=4888 width=113) (actual time=9788.066..11042.584 rows=85694 loops=1)" " Merge Cond: (""outer"".locationid = ""inner"".locationid)" " -> Sort (cost=604.65..605.45 rows=319 width=49) (actual time=5.367..6.729 rows=402 loops=1)" " Sort Key: l.locationid" " -> Index Scan using ix_location on tbllocation l (cost=0.00..591.38 rows=319 width=49) (actual time=0.193..3.548 rows=402 loops=1)" " Index Cond: ('SAKS'::text = (clientnum)::text)" " -> Sort (cost=52448.40..52710.82 rows=104970 width=75) (actual time=9782.634..10100.572 rows=85695 loops=1)" " Sort Key: a.locationid" " -> Merge Right Join (cost=39859.63..41130.10 rows=104970 width=75) (actual time=6811.114..8284.253 rows=99139 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..226.47 rows=6343 width=37) (actual time=0.089..33.082 rows=5662 loops=1)" " Filter: (1 = presentationid)" " -> Sort (cost=39859.63..40122.06 rows=104970 width=53) (actual time=6763.992..7160.587 rows=99139 loops=1)" " Sort Key: (a.clientnum)::text, a.jobtitleid" " -> Seq Scan on tblassociate a (cost=0.00..31105.34 rows=104970 width=53) (actual time=0.478..1831.000 rows=99139 loops=1)" " Filter: ((clientnum)::text = 'SAKS'::text)" "Total runtime: 11319.403 ms" 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. ------=_NextPart_000_0001_01C50BA1.0EF64D30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

I asked about this before and am still fighting for = success; any ideas I tried adding a index on jobtitle.

 

Explain analyze returns

 

QUERY PLAN

"Merge Join  (cost=3D53053.05..53652.66 = rows=3D4888 width=3D113) (actual time=3D9788.066..11042.584 rows=3D85694 = loops=3D1)"

"  Merge Cond: = (""outer"".locationid =3D ""inner"".locationid)"

"  ->  Sort  = (cost=3D604.65..605.45 rows=3D319 width=3D49) (actual time=3D5.367..6.729 rows=3D402 = loops=3D1)"

"        Sort = Key: l.locationid"

"        = ->  Index Scan using ix_location on tbllocation l  (cost=3D0.00..591.38 rows=3D319 width=3D49) (actual = time=3D0.193..3.548 rows=3D402 loops=3D1)"

"        =       Index Cond: ('SAKS'::text =3D = (clientnum)::text)"

"  ->  Sort  = (cost=3D52448.40..52710.82 rows=3D104970 width=3D75) (actual time=3D9782.634..10100.572 rows=3D85695 = loops=3D1)"

"        Sort = Key: a.locationid"

"        = ->  Merge Right Join  (cost=3D39859.63..41130.10 rows=3D104970 width=3D75) (actual = time=3D6811.114..8284.253 rows=3D99139 loops=3D1)"

"        =       Merge Cond: = (((""outer"".clientnum)::text =3D ""inner"".""?column10?"") = AND (""outer"".id =3D = ""inner"".jobtitleid))"

"        =       ->  Index Scan using = ix_tbljobtitle_id on tbljobtitle jt  (cost=3D0.00..226.47 rows=3D6343 width=3D37) = (actual time=3D0.089..33.082 rows=3D5662 loops=3D1)"

"        =             = Filter: (1 =3D presentationid)"

"        =       ->  Sort  = (cost=3D39859.63..40122.06 rows=3D104970 width=3D53) (actual time=3D6763.992..7160.587 rows=3D99139 = loops=3D1)"

"        =             Sort = Key: (a.clientnum)::text, a.jobtitleid"

"        =             = ->  Seq Scan on tblassociate a  (cost=3D0.00..31105.34 rows=3D104970 width=3D53) (actual = time=3D0.478..1831.000 rows=3D99139 loops=3D1)"

"        =             &= nbsp;     Filter: ((clientnum)::text =3D 'SAKS'::text)"

"Total runtime: 11319.403 = ms"

 

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.
© 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.

 


 

 

------=_NextPart_000_0001_01C50BA1.0EF64D30--