Re: Slow join query - Mailing list pgsql-performance

<div style="background-color:"><p>The explain is as
follows...<p>                                                                                                          
QUERYPLAN                                        <br
/>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Limit (cost=0.00..96.48 rows=1 width=2450)<br />   ->  Nested Loop  (cost=0.00..96.48 rows=1 width=2450)<br
/>        ->  Index Scan using idx_last_name on customer p  (cost=0.00..50.22 rows=1 width=1209)<br />              
IndexCond: (((last_name)::text >= 'S'::character varying) AND ((last_name)::text < 'T'::character varying) AND
((first_name)::text>= 'B'::character varying) AND ((first_name)::text < 'C'::character varying))<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_pkey on address pli  (cost=0.00..46.23
rows=1width=1257)<br />               Index Cond: (("outer".party_id = pli.party_id))<br />               Filter:
(((state)::text~~ 'M%'::text) AND ((city)::text ~~ 'AL%'::text))<br /><br /><br /><br /><br /><br /><p> <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"><hr color="#a0c6e5" size="1" /> 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 /></font></blockquote></div><br clear="all" /><hr /><a
href="http://g.msn.com/8HMBENUS/2740??PS=47575"target="_top">Picture this � share your photos and you could win
big!</a>