pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow join query
6+ messages / 3 participants
[nested] [flat]
* Slow join query
@ 2007-06-22 18:32 Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 19:51 ` Re: Slow join query Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Tamulewicz @ 2007-06-22 18:32 UTC (permalink / raw)
To: pgsql-performance
<html><div style='background-color:'><P>I have a query that runs about 30-50 seconds. The query is a join between 2 tables (customer and address), each table with about 400,000 rows. My customer table has fields like first_name and last_name where the address table has city, state, etc. I'm using "like" in most of the query columns, which all have indexes. The actual query is:<BR><BR>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<BR></P>
<P>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.</P>
<P>Q: Would this query benefit from using a view between these two tables?</P>
<P>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. </P>
<P>Suggestions very welcome,</P>
<P>Tom</P>
<P> </P></div><br clear=all><hr> <a href="http://g.msn.com/8HMAENUS/2728??PS=47575"; target="_top">Who's that on the Red Carpet? Play & win glamorous prizes.</a> </html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow join query
2007-06-22 18:32 Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
@ 2007-06-22 19:51 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:25 ` Re: Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Glaesemann @ 2007-06-22 19:51 UTC (permalink / raw)
To: Tom Tamulewicz <tomjt7@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow join query
2007-06-22 18:32 Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 19:51 ` Re: Slow join query Michael Glaesemann <grzm@seespotcode.net>
@ 2007-06-22 21:25 ` Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 21:30 ` Re: Slow join query Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:53 ` Re: Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Tamulewicz @ 2007-06-22 21:25 UTC (permalink / raw)
To: grzm@seespotcode.net; +Cc: pgsql-performance
<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@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 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>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow join query
2007-06-22 18:32 Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 19:51 ` Re: Slow join query Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:25 ` Re: Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
@ 2007-06-22 21:30 ` Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Glaesemann @ 2007-06-22 21:30 UTC (permalink / raw)
To: Tom Tamulewicz <tomjt7@hotmail.com>; +Cc: pgsql-performance
[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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow join query
2007-06-22 18:32 Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 19:51 ` Re: Slow join query Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:25 ` Re: Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
@ 2007-06-22 21:53 ` Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 22:24 ` Re: Slow join query Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Tamulewicz @ 2007-06-22 21:53 UTC (permalink / raw)
To: grzm@seespotcode.net; +Cc: pgsql-performance
<html><div style='background-color:'><P><FONT style="FONT-SIZE: 11px; FONT-FAMILY: tahoma,sans-serif"> </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 <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 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></P>
<P> </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_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 </P>
<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 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)<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) (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>
<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>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow join query
2007-06-22 18:32 Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 19:51 ` Re: Slow join query Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:25 ` Re: Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 21:53 ` Re: Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
@ 2007-06-22 22:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2007-06-22 22:24 UTC (permalink / raw)
To: Tom Tamulewicz <tomjt7@hotmail.com>; +Cc: grzm@seespotcode.net; pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-22 22:24 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-22 18:32 Slow join query Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 19:51 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:25 ` Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 21:30 ` Michael Glaesemann <grzm@seespotcode.net>
2007-06-22 21:53 ` Tom Tamulewicz <tomjt7@hotmail.com>
2007-06-22 22:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox