<html><body><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=650 width=8) <br> Index Cond: ((algorithm)::text = 'SMAT'::text)<br> -> Index Scan using idx_sars_acts_run_id_end_time on sars_acts this_ (cost=0.00..11891.29 rows=4452 width=8) <br> Index Cond: (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br>(6 rows)<br><br>serverdb=# \timing<br>TIming is on.<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 milliseconds once 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" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency.<br>
From: Jeff Janes <<a href="mailto:jeff(dot)janes(at)gmail(dot)com">jeff(dot)janes(at)gmail(dot)com</a>><br>
Date: Wed, May 22, 2013 5:17 pm<br>
To: <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a><br>
Cc: Jaime Casanova <<a href="mailto:jaime(at)2ndquadrant(dot)com">jaime(at)2ndquadrant(dot)com</a>>, psql performance list<br>
<<a href="mailto:pgsql-performance(at)postgresql(dot)org">pgsql-performance(at)postgresql(dot)org</a>>, Postgres General<br>
<<a href="mailto:pgsql-general(at)postgresql(dot)org">pgsql-general(at)postgresql(dot)org</a>><br>
<br>
<div dir="ltr">On Wed, May 22, 2013 at 7:41 AM, <span dir="ltr"><<a href="mailto:fburgess(at)radiantblue(dot)com" target="_blank">fburgess(at)radiantblue(dot)com</a>></span> wrote:<br><div class="gmail_extra"><div class="gmail_quote"> <blockquote class="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 loop from the 580 rows (it thinks) that match in <span style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN against the 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 it would 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">Could you "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><div style=""><font color="#000000"><span style="white-space:pre-wrap"><br></span></font></div><div style=""><font color="#000000"><span style="white-space:pre-wrap">Cheers,</span></font></div> <div style=""><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></body></html>