From: Bruce Momjian <bruce@momjian.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@2ndquadrant.com>
Cc: Jim Nasby <jim@nasby.net>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
Date: Tue, 11 Feb 2014 12:12:13 -0500
Message-ID: <20140211171213.GE2289@momjian.us> (raw)
In-Reply-To: <CA+TgmobeczQK0LhgVz_bSs7M4iTbCP=sfK8=6yDuCpwtOiuCCw@mail.gmail.com>
References: <20130906222623.GC626072@alap2.anarazel.de>
<20130907002908.GA12753@momjian.us>
<20130907053449.GE626072@alap2.anarazel.de>
<5238854F.5040708@nasby.net>
<20130917231050.GD29545@awork2.anarazel.de>
<CA+TgmoZQyt253OA_ssD6OVs8o0-RkRz3eK6qZz3Utwp-2QVf6A@mail.gmail.com>
<20140201032207.GD31141@momjian.us>
<CA+TgmoYOAzNKWKdkNcq50MFQKdgSt1ESR6zxb8XKCiNO6YBX2A@mail.gmail.com>
<20140211155607.GB2289@momjian.us>
<CA+TgmobeczQK0LhgVz_bSs7M4iTbCP=sfK8=6yDuCpwtOiuCCw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Tue, Feb 11, 2014 at 11:28:36AM -0500, Robert Haas wrote:
> A sequential scan will set hint bits and will prune the page, but
> pruning the page doesn't ever mark it all-visible; that logic is
> entirely in vacuum. If that could be made cheap enough to be
> negligible, it might well be worth doing in heap_page_prune(). I
> think there might be a way to do that, but it's a bit tricky because
> the pruning logic iterates over the page in a somewhat complex way,
> not just a straightforward scan of all the item pointers the way the
> existing logic doesn't. It would be pretty cool if we could just use
> a bit out of the heap-prune xlog record to indicate whether the
> all-visible bit should be set; then we'd gain the benefit of marking
> things all-visible much more often without needing vacuum.
>
> That doesn't help insert-only tables much, though, because those won't
> require pruning. We set hint bits (which dirties the page) but
> currently don't write WAL. We'd have to change that to set the
> all-visible bit when scanning such a table, and that would be
> expensive. :-(
Yes, that pretty much sums it up. We introduced index-only scans in 9.2
(2012) but they still seem to be not usable for insert-only workloads
two years later. Based on current progress, it doesn't look like this
will be corrected until 9.5 (2015). I am kind of confused why this has
not generated more urgency.
I guess my question is what approach do we want to take to fixing this?
If we are doing pruning, aren't we emitting WAL? You are right that for
an insert-only workload, we aren't going to prune, but if pruning WAL
overhead is acceptable for a sequential scan, isn't index-only
page-all-visible WAL overhead acceptable?
Do we want to track the number of inserts in statistics and trigger an
auto-vacuum after a specified number of inserts? The problem there is
that we really don't need to do any index cleanup, which is what vacuum
typically does --- we just want to scan the table and set the
all-visible bits, so that approach seems non-optimal.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruce@momjian.us, robertmhaas@gmail.com, andres@2ndquadrant.com, jim@nasby.net, alvherre@2ndquadrant.com, peter@2ndquadrant.com, tgl@sss.pgh.pa.us, andrew@dunslane.net, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] encouraging index-only scans
In-Reply-To: <20140211171213.GE2289@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox