Thread: Slow join query
<div style="background-color:"><p>I have a query that runs about 30-50 seconds. The query is a join between 2 tables (customerand address), each table with about 400,000 rows. My customer table has fields like first_name and last_name wherethe address table has city, state, etc. I'm using "like" in most of the query columns, which all have indexes. Theactual query is:<br /><br />SELECT p.party_id, p.first_name, p.last_name, pli.address1, pli.city, pli.state FROM customeras p JOIN address as pli ON ( p.party_id = pli.party_id ) WHERE ( p.void_flag IS 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<br /><p>When the query runs, the hard drive lights up for the duration. (I'm confused by thisas 'top' reports only 24k of swap in use). My SUSE 9 test machine has 512 Meg of RAM with 300 Meg used by a Java app. Postmaster reports 56 Meg under "top" and has a 52 Meg segment under "ipcs". I've played with the cache size, sharedbuffers, and OS shmmax with little change in the query performance.<p>Q: Would this query benefit from using a viewbetween these two tables?<p>Q: Any idea why the reported swap usage is so low, yet the query slams the drive? Is postgresnot caching this data? If I run the query with the same arguments, it comes right back the second time. If I changethe args and re-run, it goes back to the hard drive and takes 30-50 seconds. <p>Suggestions very welcome,<p>Tom<p> </div><brclear="all" /><hr /><a href="http://g.msn.com/8HMAENUS/2728??PS=47575" target="_top">Who's thaton the Red Carpet? Play & win glamorous prizes.</a>
On Jun 22, 2007, at 13:32 , Tom Tamulewicz wrote: > ( p.void_flag IS NULL OR p.void_flag = false ) Just a note: you can rewrite (a IS NULL or a = false) as (a IS NOT TRUE). Shouldn't affect performance, but might make your query easier to read. What's the EXPLAIN ANALYZE output for this query? > When the query runs, the hard drive lights up for the duration. > (I'm confused by this as 'top' reports only 24k of swap in use). > My SUSE 9 test machine has 512 Meg of RAM with 300 Meg used by a > Java app. Postmaster reports 56 Meg under "top" and has a 52 Meg > segment under "ipcs". I've played with the cache size, shared > buffers, and OS shmmax with little change in the query performance. > > Q: Would this query benefit from using a view between these two > tables? I doubt it, as views are just pre-parsed queries: no data is materialized for the view. > Q: Any idea why the reported swap usage is so low, yet the query > slams the drive? Is postgres not caching this data? If I run the > query with the same arguments, it comes right back the second > time. If I change the args and re-run, it goes back to the hard > drive and takes 30-50 seconds. How much is cached depends on shared_buffers, I believe. If the result is still cached, that'd explain why running the query with the same arguments returns so quickly. You might see some improvement using a prepared query, as the server shouldn't have to reparse and replan the query. Of course, if you change the arguments, it can't use the result that's cached from the previous run. Take this all with an appropriate amount of salt. I'm learning about this, too. Michael Glaesemann grzm seespotcode net
<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>
[Please don't top post as it makes the discussion more difficult to follow.] On Jun 22, 2007, at 16:25 , Tom Tamulewicz wrote: > The explain is as follows... EXPLAIN ANALYZE, please. (And for convenience, it helps if you include the query :) ) Michael Glaesemann grzm seespotcode net
<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>
Tom Tamulewicz wrote:
>
>
>
> ------------------------------------------------------------------------
>
> 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_flag IS 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
>
>
> QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..96.48 rows=1 width=2450) (actual
> time=13459.814..13459.814 rows=0 loops=1)
> -> Nested Loop (cost=0.00..96.48 rows=1 width=2450) (actual
> time=13459.804..13459.804 rows=0 loops=1)
> -> Index Scan using 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)
> 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))
> Filter: (((void_flag IS NULL) OR (void_flag =
> false)) AND ((first_name)::text ~~ 'B%'::text) AND
> ((last_name)::text ~~ 'S%'::text))
> -> Index Scan using address_pkey on address pli
> (cost=0.00..46.23 rows=1 width=1257) (actual time=0.149..0.149
> rows=0 loops=2474)
> Index Cond: (("outer".party_id = pli.party_id))
> Filter: (((state)::text ~~ 'M%'::text) AND
> ((city)::text ~~ 'AL%'::text))
> Total runtime: 13460.292 ms
>
The problem here is this bit:
-> Index Scan using 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)
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))
Filter: (((void_flag IS NULL) OR (void_flag = false)) AND
((first_name)::text ~~ 'B%'::text) AND ((last_name)::text ~~ 'S%'::text))
Note that you're getting back 2474 rows, but the planner expects 1. Not
the actual time going from 57 to 13048, it's spending all it's time
looking up each tuple in the index, then in the table. Using a seq scan
would be much faster.
Have you analyzed this table? If so, you might need to up the stats
target on last_name and see if that helps.