Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eExX0-0003z5-RV for pgsql-performance@arkaria.postgresql.org; Wed, 15 Nov 2017 13:15:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eExX0-0002Fq-BT for pgsql-performance@arkaria.postgresql.org; Wed, 15 Nov 2017 13:15:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eExUx-0004fz-8c for pgsql-performance@postgresql.org; Wed, 15 Nov 2017 13:12:59 +0000 Received: from mail-wr0-x22e.google.com ([2a00:1450:400c:c0c::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eExUq-0005ML-M0 for pgsql-performance@postgresql.org; Wed, 15 Nov 2017 13:12:55 +0000 Received: by mail-wr0-x22e.google.com with SMTP id z14so1639172wrb.8 for ; Wed, 15 Nov 2017 05:12:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=JhO9Y/6bnqH5hBYxfgZG4alhtDNoJ6ybDd+vgLzuMOg=; b=BkM3fdzC//BsQJXuZWVLPcQ77v9tK1vOV4fHNdQnadUzTeywegxScFQmVWlJhjzbXL WQzbo/rGCmjklix95WxZJix5TCWLBUe2GsHyQiJOHdHUMPshQiUpedJBZYclXMUp/zCS pkx1WznOrqN+tRgx//eLthb6hTOGt4ne7QEkOjAspHu+Af6kzJ8erSdJCIBcphceoQR5 iAw4IiJhI/KRQb0dhioin5LLc7gxtSZQPmhtnw5b0/qrzH2hy7XzIkjFe/G2uWt8hyr/ nZ+nN7HdEjcmkkNHbKnyK8N3OGtuijBMArK5GEKG2ir+qbxUlRCWFe36ThGBgpZ0pmSm LcaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=JhO9Y/6bnqH5hBYxfgZG4alhtDNoJ6ybDd+vgLzuMOg=; b=lmcg46luAKrQodI/Tw4UvAVLzTJOMaQoH9mNq1vdzCWmFduKsGn6iNxYG/TZtsm3SE mP+QP63L3eCCWjYAz7jd7zTCwcRgaYwAjlnkjxSsDPL5jUJjEMoOVs/Oyea0Kc90fnU4 jUOlThzirjFHBwJsFwuo72I70hmoZSWdgt847p6WZXgW+ixlXInCECik+1HkuNrE4g9S nutDDNyl0iBn392MSnAfS07VtqJWLRTwNPjSAM+gXpJO48xiFB/iVBB2YSVfUuICy+w8 YKoCGFQkq/Kw+jkk4ugxmVm2IrLheMMss6NT0Ig1RPoWxaL4DIGKxjFmaIJw0/yRwEja guZQ== X-Gm-Message-State: AJaThX5Mq2/Zzji85ecUu31LRiHzABUD5my/svNGvpALnlrZx3e2szki YWNehleFO3lb5g+vQTaL1qAJPYWtdrVqHtNLO1k= X-Google-Smtp-Source: AGs4zMbpbSqlG/wf0RNiknEaRIpOqfd30u0lbqynQd/HG2PuzeYQpXQFZp0ft6B9/qjWCAxP45jnFfPzgEoghBLKbeE= X-Received: by 10.223.177.135 with SMTP id q7mr9817456wra.217.1510751571059; Wed, 15 Nov 2017 05:12:51 -0800 (PST) MIME-Version: 1.0 Received: by 10.223.146.7 with HTTP; Wed, 15 Nov 2017 05:12:10 -0800 (PST) In-Reply-To: References: From: Pavel Stehule Date: Wed, 15 Nov 2017 14:12:10 +0100 Message-ID: Subject: Re: query performance issue To: Samir Magar Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045ecfdebca9f2055e0542db" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --f403045ecfdebca9f2055e0542db Content-Type: text/plain; charset="UTF-8" 2017-11-15 13:54 GMT+01:00 Samir Magar : > please find the EXPLAIN ANALYZE output. > > On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule > wrote: > >> Hi >> >> please send EXPLAIN ANALYZE output. >> >> Regards >> >> Pavel >> >> 2017-11-15 10:33 GMT+01:00 Samir Magar : >> >>> Hello, >>> I am having performance issues with one of the query. >>> The query is taking 39 min to fetch 3.5 mil records. >>> >>> I want to reduce that time to 15 mins. >>> could you please suggest something to its performance? >>> >>> server configuration: >>> CPUs = 4 >>> memory = 16 GM >>> shared_buffers = 3 GB >>> work_mem = 100MB >>> effective_cache_size = 12 GB >>> >>> we are doing the vacuum/analyze regularly on the database. >>> >>> attached is the query with its explain plan. >>> >>> There is wrong plan due wrong estimation for this query you should to penalize nested loop set enable_nestloop to off; before evaluation of this query Thanks, >>> Samir Magar >>> >>> >>> -- >>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql. >>> org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-performance >>> >>> >> > --f403045ecfdebca9f2055e0542db Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


2017-11-15 13:54 GMT+01:00 Samir Magar <samirmagar8@gmail.com&= gt;:
please find = the=C2=A0EXPLAIN ANALYZE output.

<= div class=3D"gmail_quote">On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

please send EXPL= AIN ANALYZE output.

Regards

Pavel

2017-11-15 10:33 GMT+01:00 Samir Magar <samirmag= ar8@gmail.com>:
Hello,
I= am having performance issues with one of the query.
The query is= taking 39 min to fetch 3.5 mil records.

I want to= reduce that time to 15 mins.=C2=A0
could you please suggest some= thing to its performance?

server configuration:
=C2=A0CPUs =3D 4
memory =3D 16 GM
shared_buffer= s =3D 3 GB
work_mem =3D 100MB
effective_cache_size =3D = 12 GB

we are doing the vacuum/analyze regularly on= the database.=C2=A0

attached is the query with it= s explain plan.


<= br>
There is wrong plan due wrong estimation

<= /div>
for this query you should to penalize nested loop

<= /div>
set enable_nestloop to off;

before evalu= ation of this query


T= hanks,
Samir Magar=C2=A0=C2=A0


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-pe= rformance




--f403045ecfdebca9f2055e0542db--