Re: Slow join query

<div style="background-color:"><p><font style="FONT-SIZE: 11px; FONT-FAMILY: tahoma,sans-serif"> </font><blockquote
style="PADDING-LEFT:5px; MARGIN-LEFT: 5px; BORDER-LEFT: #a0c6e5 2px solid; MARGIN-RIGHT: 0px"><div><blockquote
style="PADDING-LEFT:5px; MARGIN-LEFT: 5px; BORDER-LEFT: #a0c6e5 2px solid; MARGIN-RIGHT: 0px"><font style="FONT-SIZE:
11px;FONT-FAMILY: tahoma,sans-serif"><p><hr color="#a0c6e5" size="1" /><p>From: <i>Michael Glaesemann
<grzm@seespotcode.net></i><br/>To: <i>Tom Tamulewicz <tomjt7@hotmail.com></i><br />CC:
<i>pgsql-performance@postgresql.org</i><br/>Subject: <i>Re: [PERFORM] Slow join query</i><br />Date: <i>Fri, 22 Jun
200714:51:32 -0500</i><br />><br />>On Jun 22, 2007, at 13:32 , Tom Tamulewicz wrote:<br />>>( p.void_flag
ISNULL OR p.void_flag = false )<br />>Just a note: you can rewrite (a IS NULL or a = false) as (a IS NOT <br
/>>TRUE).Shouldn't affect performance, but might make your query <br />>easier to read.<br />><br />>What's
theEXPLAIN ANALYZE output for this query?<br />>>When the query runs, the hard drive lights up for the duration.
<br/>>>(I'm confused by this as 'top' reports only 24k of swap in use). <br />>>My SUSE 9 test machine has
512Meg of RAM with 300 Meg used by a <br />>>Java app. Postmaster reports 56 Meg under "top" and has a 52 Meg <br
/>>>segmentunder "ipcs". I've played with the cache size, shared <br />>>buffers, and OS shmmax with little
changein the query performance.<br />>><br />>>Q: Would this query benefit from using a view between these
two<br />>>tables?<br />>I doubt it, as views are just pre-parsed queries: no data is <br />>materialized
forthe view.<br />>>Q: Any idea why the reported swap usage is so low, yet the query <br />>>slams the
drive?Is postgres not caching this data? If I run the <br />>>query with the same arguments, it comes right back
thesecond <br />>>time. If I change the args and re-run, it goes back to the hard <br />>>drive and takes
30-50seconds.<br />>How much is cached depends on shared_buffers, I believe. If the <br />>result is still
cached,that'd explain why running the query with <br />>the same arguments returns so quickly. You might see some
<br/>>improvement using a prepared query, as the server shouldn't have to <br />>reparse and replan the query. Of
course,if you change the <br />>arguments, it can't use the result that's cached from the previous <br />>run.<br
/>><br/>>Take this all with an appropriate amount of salt. I'm learning about <br />> this, too.<br />><br
/>>MichaelGlaesemann<br />>grzm seespotcode net<br />><br />><br />><br
/>>---------------------------(endof <br />>broadcast)---------------------------<br />>TIP 9: In versions
below8.0, the planner will ignore your desire to<br />> choose an index scan if your joining column's datatypes do
<br/>>not<br />> match<br /><p> </font></blockquote></div><p>SELECT p.party_id, p.first_name, p.last_name,
pli.address1,pli.city, pli.state FROM customer as p JOIN address as pli ON ( p.party_id = pli.party_id ) WHERE (
p.void_flagIS NULL OR p.void_flag = false )  AND  (first_name like 'B%') AND (last_name like 'S%') AND (pli.state like
'M%')AND (pli.city like 'AL%') ORDER BY last_name, first_name LIMIT
51  <p>                                                                                                    QUERY
PLAN                                       <br
/>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Limit (cost=0.00..96.48 rows=1 width=2450) (actual time=13459.814..13459.814 rows=0 loops=1)<br />   ->  Nested
Loop (cost=0.00..96.48 rows=1 width=2450) (actual time=13459.804..13459.804 rows=0 loops=1)<br />         ->  Index
Scanusing idx_last_name on customer p  (cost=0.00..50.22 rows=1 width=1209) (actual time=57.812..13048.524 rows=2474
loops=1)<br/>               Index Cond: (((last_name)::text >= 'S'::character varying) AND ((last_name)::text <
'T'::charactervarying) AND ((first_name)::text >= 'B'::character varying) AND ((first_name)::text <
'C'::charactervarying))<br />               Filter: (((void_flag IS NULL) OR (void_flag = false)) AND
((first_name)::text~~ 'B%'::text) AND ((last_name)::text ~~ 'S%'::text))<br />         ->  Index Scan using
address_pkeyon address pli  (cost=0.00..46.23 rows=1 width=1257) (actual time=0.149..0.149 rows=0 loops=2474)<br
/>              Index Cond: (("outer".party_id = pli.party_id))<br />               Filter: (((state)::text ~~
'M%'::text)AND ((city)::text ~~ 'AL%'::text))<br /> Total runtime: 13460.292 ms<br /><p><br clear="all" /><hr /><a
href="http://g.msn.com/8HMBENUS/2740??PS=47575">Picturethis � share your photos and you could win big!</a><br
/></blockquote></div><brclear="all" /><hr /><a href="http://g.msn.com/8HMBENUS/2743??PS=47575" target="_top">Get a
previewof Live Earth, the hottest event this summer - only on MSN</a>