Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ieAQ8-0002xl-VS for pgsql-performance@arkaria.postgresql.org; Mon, 09 Dec 2019 04:13:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ieAQ6-00062l-FO for pgsql-performance@arkaria.postgresql.org; Mon, 09 Dec 2019 04:13:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ieAQ5-00062d-Uc for pgsql-performance@lists.postgresql.org; Mon, 09 Dec 2019 04:13:14 +0000 Received: from mail-yb1-xb44.google.com ([2607:f8b0:4864:20::b44]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ieAQ2-0001ZP-LD for pgsql-performance@lists.postgresql.org; Mon, 09 Dec 2019 04:13:12 +0000 Received: by mail-yb1-xb44.google.com with SMTP id i3so5602802ybe.12 for ; Sun, 08 Dec 2019 20:13:10 -0800 (PST) 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:content-transfer-encoding:in-reply-to :user-agent; bh=fXLE3vOYe1V8Bi9XdLHxS/Hyjj+7ybF3kn3nkay9N3Q=; b=QT9Ah5dOLytYx1TJ2m+wUriOyO4QzlDIENliki0DngLQmaZ6qzZcoqxrRMzetIi1vB 3LyHtNxFWQeanTnuCIVvFUKCE/HkazWALK6soQ92e5aUvN/Yx2c5gbAbeNO8idW/6ZzC InhiXolezI+KfdfmIJVZOlm9DNPxlkQS1EAbxW175kYfa9hE6bNluJDbubVxu/IvptEX UtYZleweSIiqVanqPNnk4TqPcF33b6TgJpEyqPoP08uC5k8UaZhzik06ItUSB2IpZYPO rZgyNvD9PhjY7/ozp09CGplUTVt+GGxbBxg1/CzxFBOYWzCwN5fBO1lgiB0S4ihyDapt ZPCg== 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:content-transfer-encoding :in-reply-to:user-agent; bh=fXLE3vOYe1V8Bi9XdLHxS/Hyjj+7ybF3kn3nkay9N3Q=; b=CfTwIUwTKPGK3xCyJHgIG7Jofo2jElAKZUaIuhQwtXJC1T280j+jePPPNHuVenFXse oGjsiC/nOkM1sjQH+R9U2wfbUGP1fgsfTH6w291zHWj2A3mH9Ra6brjxfQ8+xgCTe2Z0 nw0ByUYg+zB4Nm7nkiR83dBwT0Lyv88rUIvEJJtxy4psluanpp4yMwr6VV3IpmHmxOIC G3in/1lGayI/gMjX/IAtBO5vtoz4o/A7fP6cvvLN1dYasYRnSEZIyvDKkM+xF31/KcSK aS5c7qjfrGodz+XbP+2/B7C+2EilzeRyni/6YbYHtNCDleVCXYGel3XnfZuFVV7FqFa3 JXBA== X-Gm-Message-State: APjAAAWigL312Tv9Qz00M1bGsU2NSzZylH9wxLZHQuORdwzBks0uQfBT h6tCyhvrCAVz06r99H4pCGRdTEqUgO8= X-Google-Smtp-Source: APXvYqxe2D+LSC80wkleZZ3d1vxD6x7JIPzaemxjyaH88dAbtMFoEwyc81GFgyyZcsNKShlO2xLZFQ== X-Received: by 2002:a25:ad1c:: with SMTP id y28mr2592129ybi.94.1575864789501; Sun, 08 Dec 2019 20:13:09 -0800 (PST) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id g131sm7318398ywb.35.2019.12.08.20.13.08 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sun, 08 Dec 2019 20:13:08 -0800 (PST) Received: by pryzbyj (Postfix, from userid 1000) id BA982800A81; Sun, 8 Dec 2019 22:13:07 -0600 (CST) Date: Sun, 8 Dec 2019 22:13:07 -0600 From: Justin Pryzby To: Fahiz Mohamed Cc: pgsql-performance@lists.postgresql.org Subject: Re: Specific query taking time to process Message-ID: <20191209041307.GW2082@telsasoft.com> References: <6bd9b9b7-f127-4e48-a456-b3bbcfb741ab@Spark> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Sat, Dec 07, 2019 at 08:05:59PM +0000, Fahiz Mohamed wrote: > There is a specific search query I am running to get list of Documents and their metadata from several table in the DB. > We are running Postgres 9.6.9 on Amazon RDS (db.m5.4xlarge instance) > > Our current DB consists of 500GB of data and indexes. Most of the rows in table are consist of 454,078,915 454M rows or ?? > With the fresh DB with the restore of the DATA without any indexes Search query performs relatively quick and most of the time its less than a second. > But after 3 weeks of use of the DB it sudenly started to slowdown only for this perticular query and it takes 20+ seconds to respond. If I do a restore the DB again then it continues to work fine and the symptom pops out after 3 weeks time. > > I am just suspecting is there any cache or index maxing out causes this issue? > > Could you please guide me what can it be the root cause of this issue? https://wiki.postgresql.org/wiki/Slow_Query_Questions Could you send explain ANALYZE (attach here as txt attachment or link on depesz) now and compared with shortly after a restore ? Justin