Re: [PERFORM] Very slow inner join query Unacceptable latency.

<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>serverdb=# set enable_hashjoin=off;<br />SET<br
/>serverdb=#explain select count(*) as y0_ from SARS_ACTS this_ inner join <span
style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN</span>tr1_ on this_.SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>where tr1.ALGORITHM='SMAT';<br /><br
/>                                                   QUERY PLAN<br
/>--------------------------------------------------------------------------------------------------------------------------<br
/>Aggregate (cost=7765563.69..7765563.70 rows=1 width=0) <br />      Nested Loop  (cost=0.00..7765555.35 rows=3336
width=0)<br/>         -> Index Scan using idx_sars_acts_run_algorithm on sars_acts_run tr1_  (cost=0.00..44.32
rows=650width=8) <br />              Index Cond:  ((algorithm)::text = 'SMAT'::text)<br />         -> Index Scan
usingidx_sars_acts_run_id_end_time on sars_acts this_  (cost=0.00..11891.29 rows=4452 width=8) <br />             
IndexCond:  (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br />(6 rows)<br /><br />serverdb=# \timing<br />TIming
ison.<br /><br />serverdb=# select count(*) as y0_ from SARS_ACTS this_ inner join <span
style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN</span>tr1_ on this_.SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>where tr1.ALGORITHM='SMAT';<br /> y0_<br />------<br />1481710<br />(1 row)<br /><br
/>Time:85069.416 ms < 1.4 minutes <-- not great, but much better!<br /><br />Subsequently, runs in the
millisecondsonce cached.</div><div><br /></div><div>But what negative impact is disabling hash joins?</div><div><br
/></div><div>Sorry,I just executed the explain without the analyze, I'll send out the "explain analyze" next
reply.</div><div><br/></div><div>thanks</div><div><br /></div><div>Freddie<br /></div><div><br /></div><blockquote
id="replyBlockquote"style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt;
color:black;font-family:verdana;" webmail="1"><div id="wmQuoteWrapper"> -------- Original Message --------<br />
Subject:Re: [PERFORM] Very slow inner join query Unacceptable latency.<br /> From: Jeff Janes <<a
href="mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>><br/> Date: Wed, May 22, 2013 5:17 pm<br /> To: <a
href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><br/> Cc: Jaime Casanova <<a
href="mailto:jaime@2ndquadrant.com">jaime@2ndquadrant.com</a>>,psql performance list<br /> <<a
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>>,Postgres General<br /> <<a
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br/><br /><div dir="ltr">On Wed, May
22,2013 at 7:41 AM, <span dir="ltr"><<a href="mailto:fburgess@radiantblue.com"
target="_blank">fburgess@radiantblue.com</a>></span>wrote:<br /><div class="gmail_extra"><div
class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><span
style="font-size:10pt;font-family:Verdana"><div>PostgreSQL 9.1.6 on linux<br /></div></span></div></blockquote><div><br
/></div><div><br/></div><div style="">From the numbers in your attached plan, it seems like it should be doing a nested
loopfrom the 580 rows (it thinks) that match in <span style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN
againstthe index on sars_run_id to pull out the </span><span style="color:rgb(0,0,0);white-space:pre-wrap">3297 rows
(again,it think, though it is way of there)</span><span style="color:rgb(0,0,0);white-space:pre-wrap">. I can't see why
itwould not do that. There were some planner issues in the early 9.2 releases that caused very large indexes to be
punished,but I don't think those were in 9.1</span></div><div style=""><span
style="color:rgb(0,0,0);white-space:pre-wrap"><br/></span></div><div style=""><font color="#000000"><span
style="white-space:pre-wrap">Couldyou "set enable_hashjoin to off" and post the "explain analyze" that that
gives?</span></font><br/></div><div style=""><font color="#000000"><span style="white-space:pre-wrap"><br
/></span></font></div><divstyle=""><font color="#000000"><span style="white-space:pre-wrap"><br
/></span></font></div><divstyle=""><font color="#000000"><span
style="white-space:pre-wrap">Cheers,</span></font></div><divstyle=""><font color="#000000"><span
style="white-space:pre-wrap"><br/></span></font></div><div style=""><font color="#000000"><span
style="white-space:pre-wrap">Jeff</span></font></div><div> </div></div></div></div></div></blockquote></span>