Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fCe8V-0005nn-0R for pgsql-performance@arkaria.postgresql.org; Sun, 29 Apr 2018 04:40:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fCe8R-0001oB-Mv for pgsql-performance@arkaria.postgresql.org; Sun, 29 Apr 2018 04:40:27 +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.89) (envelope-from ) id 1fCe8R-0001o2-B7 for pgsql-performance@lists.postgresql.org; Sun, 29 Apr 2018 04:40:27 +0000 Received: from mail-it0-x233.google.com ([2607:f8b0:4001:c0b::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fCe8N-0000eP-UA for pgsql-performance@lists.postgresql.org; Sun, 29 Apr 2018 04:40:26 +0000 Received: by mail-it0-x233.google.com with SMTP id 70-v6so360534ity.2 for ; Sat, 28 Apr 2018 21:40:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=6oNwASRjQBv4ysrVfScpOpswcFDz1mfJ+Y4kOw0vmTY=; b=MVphybG2e/EZR8wWIvs2xr2VnCRI8YIl1qdC8i+7PVQvHrYJeCsliQIwxdwM5N5W/8 DrwTZD3PxNsWNYzGHAkW7pDWM+gOKxNkdDaxD14zQEcoF+f9wbj5OInBTN9wvpFjkdAL t43OsNmqwqO6ZgMqgetLtdYnRjK2lr3KOWCqCakMRxZxfzeuTrNlj1LPjfbGsbcQ9yth WwHUhrBzdgpAW+Rwi8ec4O/Sq8dLVf8TzR38PU9cVw1mwcqZhMYWY7MKWJo0YtP+8Yuz asbJl5z2TRTV5AAnqV+J/zbzCgjfxKGJXyjMOe6KsoNpZQZIl1WOy5iNOtfWvWYX4q9I zepw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=6oNwASRjQBv4ysrVfScpOpswcFDz1mfJ+Y4kOw0vmTY=; b=itwNOTBJ9MSrG2gfSNirO8qJgEVCDhUDapQOZPWkmTEpKE937Svt3/9P+CeyzKNimy YNNnAWTAbHQl8mcIVwEDpp0SkdpO+oeuQBSho11ovUa+8X+4PXEvIjwSSL9dhWenZffm +TLYrFfJeYDoXUpo4cmE6RDYxzdORx4sQfhqc65FEgvhTa+s6SEO4yQesy/L+OcTTdob qLaF62aXsI79kGjvGmPBjwb5DYqixAjIu6qozFWiGVixb/d7U7lCbD94UtQI4G/bEPvs 8fPSej2+vmsts/m4txVyzUAzAhhNIScuYQcz1jyyfs99nvQqqPYW+fYj7K+2e+V+4bE9 YJjQ== X-Gm-Message-State: ALQs6tCK8YFKZ5IJLQafarb7h+3RaPBhgTTLOh0trlYuGLYFHAgrTndS YwtVqMXkG1jsXYyDap/+RIJYgw== X-Google-Smtp-Source: AB8JxZo8MM+xSiS6AeI4rQfNZqguC2s+GknerAz+nGoRgfVnHm+jfbIGwHin1RsLtuDKQLQSrIQ1ag== X-Received: by 2002:a24:1a85:: with SMTP id 127-v6mr6951862iti.143.1524976820756; Sat, 28 Apr 2018 21:40:20 -0700 (PDT) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id 13-v6sm2325717ioh.15.2018.04.28.21.40.20 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 28 Apr 2018 21:40:20 -0700 (PDT) Received: by pryzbyj (Postfix, from userid 1000) id 84A9C801F5E; Sat, 28 Apr 2018 23:40:19 -0500 (CDT) Date: Sat, 28 Apr 2018 23:40:19 -0500 From: Justin Pryzby To: Kaushal Shriyan Cc: pgsql-performance@lists.postgresql.org Subject: Re: Performance issues while running select sql query Message-ID: <20180429044019.GO20071@telsasoft.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote: > Hi, > > I am running postgresql db server 9.4.14 on AWS of C4.2xlarge instance type > with 500 GB volume of volume type io1 with 25000 IOPS and I am seeing > performance issues. The sql query response takes around *127713.413 ms *time > *.* Is there a way to find out the bottleneck? Send the output of "explain(analyze,buffers)" for the query? Justin