<html><div style='background-color:'><P>The explain is as follows...</P>
<P> QUERY PLAN
<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> Index Cond: (((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=1 width=1257)<BR> Index Cond: (("outer".party_id = pli.party_id))<BR> Filter: (((state)::text ~~ 'M%'::text) AND ((city)::text ~~ 'AL%'::text))<BR><BR><BR></P><BR><BR><BR>
<P> </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(at)seespotcode(dot)net></I><BR>To: <I>Tom Tamulewicz <tomjt7(at)hotmail(dot)com></I><BR>CC: <I>pgsql-performance(at)postgresql(dot)org</I><BR>Subject: <I>Re: [PERFORM] Slow join query</I><BR>Date: <I>Fri, 22 Jun 2007 14:51:32 -0500</I><BR>><BR>>On Jun 22, 2007, at 13:32 , Tom Tamulewicz wrote:<BR>>>( p.void_flag IS NULL 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 the EXPLAIN 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 512 Meg of RAM with 300 Meg used by a <BR>>>Java app. Postmaster reports 56 Meg under
"top" and has a 52 Meg <BR>>>segment under "ipcs". I've played with the cache size, shared <BR>>>buffers, and OS shmmax with little change in 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 for the 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 the second <BR>>>time. If I change the args and re-run, it goes back to the hard <BR>>>drive and takes 30-50 seconds.<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>>Michael Glaesemann<BR>>grzm seespotcode net<BR>><BR>><BR>><BR>>---------------------------(end of <BR>>broadcast)---------------------------<BR>>TIP 9: In versions below 8.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> </html>