<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>We re-tested these settings a few times after our initial test and realized that the execution time I posted was shewed, because the execution plan was cached after the initial run. Subsequent executions ran in a little over a second.</div>
<div>There ended up being no significant saving by setting these parameters. Un-cached the query ran in about 55 seconds. </div>
<div> </div>
<BLOCKQUOTE style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=replyBlockquote 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(dot)marlowe(at)gmail(dot)com">scott(dot)marlowe(at)gmail(dot)com</a>><BR>Date: Fri, May 24, 2013 3:03 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>On Fri, May 24, 2013 at 3:44 PM, <<a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a>> wrote:<BR><BR>> Total runtime: 1606.728 ms 1.6 seconds <- very good response time<BR>> improvement<BR>><BR>> (7 rows)<BR>><BR>> Questions:<BR>><BR>> Any concerns with setting these conf variables you recommended; work_mem,<BR>> random_page_cost dbserver wide (in postgresql,conf)?<BR>><BR>> Thanks so much!!!<BR><BR>Yes 500MB is pretty high especially if you have a lot of connections.<BR>Try it with it back down to 16MB and see how it does. Work mem is per<BR>sort so a setting as high as 500MB can exhaust memory on the machine<BR>under heavy load.<BR><BR>--<BR>To understand recursion, one must first understand recursion.<BR></DIV></BLOCKQUOTE></span></body></html>