pgsql: Improve performance of get_actual_variable_range with recently-d

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Improve performance of get_actual_variable_range with recently-d
Date: 2017-09-07 23:41:58
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-committers pgsql-hackers

Improve performance of get_actual_variable_range with recently-dead tuples.

In commit fccebe421, we hacked get_actual_variable_range() to scan the
index with SnapshotDirty, so that if there are many uncommitted tuples
at the end of the index range, it wouldn't laboriously scan through all
of them looking for a live value to return. However, that didn't fix it
for the case of many recently-dead tuples at the end of the index;
SnapshotDirty recognizes those as committed dead and so we're back to
the same problem.

To improve the situation, invent a "SnapshotNonVacuumable" snapshot type
and use that instead. The reason this helps is that, if the snapshot
rejects a given index entry, we know that the indexscan will mark that
index entry as killed. This means the next get_actual_variable_range()
scan will proceed past that entry without visiting the heap, making the
scan a lot faster. We may end up accepting a recently-dead tuple as
being the estimated extremal value, but that doesn't seem much worse than
the compromise we made before to accept not-yet-committed extremal values.

The cost of the scan is still proportional to the number of dead index
entries at the end of the range, so in the interval after a mass delete
but before VACUUM's cleaned up the mess, it's still possible for
get_actual_variable_range() to take a noticeable amount of time, if you've
got enough such dead entries. But the constant factor is much much better
than before, since all we need to do with each index entry is test its
"killed" bit.

We chose to back-patch commit fccebe421 at the time, but I'm hesitant to
do so here, because this form of the problem seems to affect many fewer
people. Also, even when it happens, it's less bad than the case fixed
by commit fccebe421 because we don't get the contention effects from
expensive TransactionIdIsInProgress tests.

Dmitriy Sarafannikov, reviewed by Andrey Borodin




Modified Files
src/backend/access/heap/heapam.c | 3 +++
src/backend/utils/adt/selfuncs.c | 40 +++++++++++++++++++++++++++-------------
src/backend/utils/time/tqual.c | 22 ++++++++++++++++++++++
src/include/utils/snapshot.h | 4 +++-
src/include/utils/tqual.h | 10 ++++++++++
5 files changed, 65 insertions(+), 14 deletions(-)


Browse pgsql-committers by date

  From Date Subject
Next Message Robert Haas 2017-09-08 01:15:41 pgsql: Refactor get_partition_for_tuple a bit.
Previous Message Tom Lane 2017-09-07 18:04:56 pgsql: Improve documentation about behavior of multi-statement Query me

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-09-07 23:43:11 Re: [PROPOSAL] Use SnapshotAny in get_actual_variable_range
Previous Message Magnus Hagander 2017-09-07 23:32:16 Re: log_destination=file