Re: Slow join query

From: "Tom Tamulewicz" <tomjt7(at)hotmail(dot)com>
To: grzm(at)seespotcode(dot)net
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow join query
Date: 2007-06-22 21:53:17
Message-ID: BAY140-F37C384351E25AC87580FA290170@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Attachment Content-Type Size
unknown_filename text/html 6.2 KB

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2007-06-22 22:24:07 Re: Slow join query
Previous Message Rainer Bauer 2007-06-22 21:51:15 Re: Data transfer very slow when connected via DSL