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 1er1Ge-0001HF-E4 for pgsql-hackers@arkaria.postgresql.org; Wed, 28 Feb 2018 12:55:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er1Gc-0003nI-P8 for pgsql-hackers@arkaria.postgresql.org; Wed, 28 Feb 2018 12:55:30 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er1Gc-0003n9-BY for pgsql-hackers@lists.postgresql.org; Wed, 28 Feb 2018 12:55:30 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er1GU-0000Dz-Cg for pgsql-hackers@lists.postgresql.org; Wed, 28 Feb 2018 12:55:28 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 41D5621C49D4; Wed, 28 Feb 2018 15:55:20 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.27.132] (gw.postgrespro.ru [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id EFF9F21C49C3; Wed, 28 Feb 2018 15:55:19 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1519822520; bh=ittQdZq5JwfTd2hgnvov2+mk5i2Bv0W+i/hhHu/MlxE=; h=Subject:To:References:From:Date:In-Reply-To; b=URDzgOYi88gDv7DJ6outrqZyG7VePFTvkrWtJPryLPlACJQc8Yg43OnqOVLpDtPY2 eTeNhNNh2Elu+Hs+Vqch6xB67AYEzTeuZnhSsmVf80fbtEVXmsrv5CvG+uU3tolMcf IKV7fr/ET1f8McvGjqQ0/TcOfWDP5nCIUzZB4OmQ= Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate. To: David Gould , pgsql-hackers@lists.postgresql.org, Alina Alexeeva , Ullas Lakkur Raghavendra References: <20180117164916.3fdcf2e9@engels> From: Alexander Kuzmenkov Message-ID: Date: Wed, 28 Feb 2018 15:55:19 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.5.0 MIME-Version: 1.0 In-Reply-To: <20180117164916.3fdcf2e9@engels> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi David, I was able to reproduce the problem using your script. analyze_counts.awk is missing, though. The idea of using the result of ANALYZE as-is, without additional averaging, was discussed when vac_estimate_reltuples() was introduced originally. Ultimately, it was decided not to do so. You can find the discussion in this thread: https://www.postgresql.org/message-id/flat/BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw%40mail.gmail.com#BANLkTinL6QuAm_Xf8teRZboG2Mdy3dR_vw@mail.gmail.com The core problem here seems to be that this calculation of moving average does not converge in your scenario. It can be shown that when the number of live tuples is constant and the number of pages grows, the estimated number of tuples will increase at each step. Do you think we can use some other formula that would converge in this scenario, but still filter the noise in ANALYZE results? I couldn't think of one yet. -- Alexander Kuzmenkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company