Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X7vMx-0005Ec-PP for pgsql-general@arkaria.postgresql.org; Thu, 17 Jul 2014 23:45:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X7vMw-00049s-R5 for pgsql-general@arkaria.postgresql.org; Thu, 17 Jul 2014 23:45:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X7vMv-00049j-3f for pgsql-general@postgresql.org; Thu, 17 Jul 2014 23:45:45 +0000 Received: from hogranch.com ([75.101.82.47]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X7vMr-0007Wj-A0 for pgsql-general@postgresql.org; Thu, 17 Jul 2014 23:45:43 +0000 Received: from [192.168.0.2] (porker [192.168.0.2]) by hogranch.com (8.11.6/8.11.6) with ESMTP id s6HNjWZ04497 for ; Thu, 17 Jul 2014 16:45:32 -0700 Message-ID: <53C86017.7020903@hogranch.com> Date: Thu, 17 Jul 2014 16:45:27 -0700 From: John R Pierce User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: Checkpoint_segments optimal value References: <20140717140103.4e8c05e8ef5b89817b4d160a@potentialtech.com> <7639.1405620388@sss.pgh.pa.us> <20140717150035.6822faed729cbd871edcc5ad@potentialtech.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050504060603080200060904" X-HawgScanner-Information: Please contact the ISP for more information X-HawgScanner: Found to be clean X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multi-part message in MIME format. --------------050504060603080200060904 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 7/17/2014 4:26 PM, Prabhjot Sheena wrote: > Here is the explain plan. There query time went backup up to 2 to 3 > minutes from 3 second in just 2 hrs. Can anyone suggest something on > how to fix this or why this is happening > > explain SELECT account.id , > account.organization_id, run.application_id, work_unit.script, > work_unit.id , work_unit.start_time, run.id > , work_unit.priority FROM work_unit, run, account > WHERE work_unit.status = 3 AND work_unit.run_id = run.id > AND work_unit.type != 1 AND run.status = 1 AND > run.account_id = account.id ; you need to use EXPLAIN ANALYZE to get accurate data. run it when the query is fast, and again when the query is slow, paste both outputs here. also, you can paste them to http://explain.depesz.com and that will give you a nice analysis of the timing data included in the EXPLAIN ANALYZE output. -- john r pierce 37N 122W somewhere on the middle of the left coast --------------050504060603080200060904 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 7/17/2014 4:26 PM, Prabhjot Sheena wrote:
Here is the explain plan. There query time went backup up to 2 to 3 minutes from 3 second  in just 2 hrs.  Can anyone suggest something on how to fix this or why this is happening

 explain SELECT account.id, account.organization_id, run.application_id, work_unit.script, work_unit.id, work_unit.start_time, run.id, work_unit.priority  FROM work_unit, run, account  WHERE  work_unit.status = 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status = 1 AND run.account_id = account.id;

you need to use EXPLAIN ANALYZE to get accurate data.

run it when the query is fast, and again when the query is slow, paste both outputs here.   also, you can paste them to http://explain.depesz.com and that will give you a nice analysis of the timing data included in the EXPLAIN ANALYZE output.




-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast
--------------050504060603080200060904--