Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VHK8T-0002Fi-4H for pgsql-hackers@arkaria.postgresql.org; Wed, 04 Sep 2013 20:57:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VHK8S-00055a-Db for pgsql-hackers@arkaria.postgresql.org; Wed, 04 Sep 2013 20:57:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VHK8Q-00051n-3A for pgsql-hackers@postgresql.org; Wed, 04 Sep 2013 20:57:06 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VHK8J-0002LB-75 for pgsql-hackers@postgresql.org; Wed, 04 Sep 2013 20:57:05 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1VHK8F-0006yx-BP; Wed, 04 Sep 2013 16:56:55 -0400 Date: Wed, 4 Sep 2013 16:56:55 -0400 From: Bruce Momjian To: Peter Geoghegan Cc: Tom Lane , Andrew Dunstan , PostgreSQL-development Subject: Re: [PERFORM] encouraging index-only scans Message-ID: <20130904205655.GR21874@momjian.us> References: <50C8F1EC.9010801@dunslane.net> <15321.1355347953@sss.pgh.pa.us> <50C90154.8060103@dunslane.net> <50C904DB.9090305@dunslane.net> <20121213024837.GB22363@momjian.us> <23298.1355370698@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -4.3 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On Thu, Dec 13, 2012 at 03:31:06PM +0000, Peter Geoghegan wrote: > On 13 December 2012 03:51, Tom Lane wrote: > > ANALYZE does not set that value, and is not going to start doing so, > > because it doesn't scan enough of the table to derive a trustworthy > > value. > > I'm slightly surprised by your remarks here, because the commit > message where the relallvisible column was added (commit > a2822fb9337a21f98ac4ce850bb4145acf47ca27) says: > > "Add a column pg_class.relallvisible to remember the number of pages > that were all-visible according to the visibility map as of the last > VACUUM > (or ANALYZE, or some other operations that update pg_class.relpages). > Use relallvisible/relpages, instead of an arbitrary constant, to > estimate how many heap page fetches can be avoided during an > index-only scan." > > Have I missed some nuance? I am looking back at this issue now and I think you are correct. The commit you mention (Oct 7 2011) says ANALYZE updates the visibility map, and the code matches that: if (!inh) vac_update_relstats(onerel, RelationGetNumberOfBlocks(onerel), totalrows, --> visibilitymap_count(onerel), hasindex, InvalidTransactionId); so if an index scan was not being used after an ANALYZE, it isn't a bad allvisibile estimate but something else. This code was in PG 9.2. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers