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

<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div><br />1.) Server setting<br /><br />memory:
32960116kB= 32GB<br /><br /><br />2.) Current Postgresql configuration settings of note in my environment.<br /><br
/>enable_hashjoin=off<br/>work_mem = 16MB <br />#random_page_cost-4.0 <- default<br />maintenance_work_mem=256MB<br
/>shared_buffers= 8GB<br /><br /><br />serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner
joinSARS_ACTS_RUN 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=5714258.72..5714258.73 rows=1 width=0) (actual time=54402.148..54402.148 rows=1 loops=1)<br />    
NestedLoop  (cost=0.00..5714253.25 rows=2188 width=0) (actual time=5.920..54090.676 rows=1481710 loops=1)<br />        
->Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_  (cost=0.00..32.71 rows=442 width=8) (actual
time=1.423..205.256rows=441 loops=1)<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..12874.40 rows=4296 width=8) (actual
time=749..121.125rows=3360 loops=441)<br />              Index Cond:  (SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>)<br/>Total runtime: 54402.212 ms <- 54 seconds<br /><br />(7 rows)<br /><br />3.)
Settingthe recommended parameters<br /><br /><br />serverdb=# set work_mem='500MB';<br />SET<br />serverdb=# set
random_page_cost=1.2;<br/>SET<br />serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join
SARS_ACTS_RUNtr1_ 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=1754246.85..1754246.86 rows=1 width=0) (actual time=1817.644..1817.644 rows=1 loops=1)<br />     
NestedLoop  (cost=0.00..1754241.38 rows=2188 width=0) (actual time=0.135..1627.954 rows=1481710 loops=1)<br />        
->Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_  (cost=0.00..22.40 rows=442 width=8) (actual
time=0.067..0.561rows=441 loops=1)<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..3915.12 rows=4296 width=8) (actual
time=0.008..2.972rows=3360 loops=441)<br />              Index Cond:  (SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>)<br/>Total runtime: 1817.695 ms  1.8 seconds <- very good response time
improvement<br/><br />(7 rows)<br /><br />4.) Now toggling the enable_hashjoin, I suspect the plan is cached, so these
resultsmay be suspect.<br /><br /><br />serverdb=# set enable_hashjoin=on;<br />SET<br />serverdb=# explain analyze
selectcount(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN 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=1754246.85..1754246.86 rows=1 width=0) (actual time=1606.683..1606.683 rows=1 loops=1)<br />     
NestedLoop  (cost=0.00..1754241.38 rows=2188 width=0) (actual time=0.136..1442.463 rows=1481710 loops=1)<br />        
->Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_  (cost=0.00..22.40 rows=442 width=8) (actual
time=0.068..0.591rows=441 loops=1)<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..3915.12 rows=4296 width=8) (actual
time=0.007..2.659rows=3360 loops=441)<br />              Index Cond:  (SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>)<br/>Total runtime: 1606.728 ms 1.6 seconds <- very good response time
improvement<br/><br />(7 rows)<br /><br />Questions:<br /><br />Any concerns with setting these conf variables you
recommended;work_mem, random_page_cost dbserver wide (in postgresql,conf)? <br /><br />Thanks so much!!!<br /><br
/></div><blockquoteid="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: [GENERAL] [PERFORM] Very slow inner join query Unacceptable<br /> latency.<br /> From: Scott
Marlowe<<a href="mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com</a>><br /> Date: Thu, May 23, 2013 11:16
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 /> Looking at the execution
planmakes me wonder what your work_mem is<br /> set to. Try cranking it up to test and lowering random_page_cost:<br
/><br/> set work_mem='500MB';<br /> set random_page_cost=1.2;<br /> explain analyze select ...<br /><br /> and see what
youget.<br /></div></blockquote></span>