8000 HOT updates. When we update a tuple without changing any of its indexed · postgrespro/postgres_cluster@282d2a0 · GitHub
[go: up one dir, main page]
Skip to content

Commit 282d2a0

Browse files
tglsfdctglsfdc
committed
HOT updates. When we update a tuple without changing any of its indexed
columns, and the new version can be stored on the same heap page, we no longer generate extra index entries for the new version. Instead, index searches follow the HOT-chain links to ensure they find the correct tuple version. In addition, this patch introduces the ability to "prune" dead tuples on a per-page basis, without having to do a complete VACUUM pass to recover space. VACUUM is still needed to clean up dead index entries, however. Pavan Deolasee, with help from a bunch of other people.
1 parent bbf4fdc commit 282d2a0

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

65 files changed

+3512
-509
lines changed

‎contrib/pgstattuple/pgstattuple.c‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/contrib/pgstattuple/pgstattuple.c,v 1.29 2007/09/12 22:10:25 tgl Exp $
2+
* $PostgreSQL: pgsql/contrib/pgstattuple/pgstattuple.c,v 1.30 2007/09/20 17:56:30 tgl Exp $
33
*
44
* Copyright (c) 2001,2002 Tatsuo Ishii
55
*
@@ -290,7 +290,7 @@ pgstat_heap(Relation rel, FunctionCallInfo fcinfo)
290290
{
291291
buffer = ReadBuffer(rel, block);
292292
LockBuffer(buffer, BUFFER_LOCK_SHARE);
293-
stat.free_space += PageGetFreeSpace((Page) BufferGetPage(buffer));
293+
stat.free_space += PageGetHeapFreeSpace((Page) BufferGetPage(buffer));
294294
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
295295
ReleaseBuffer(buffer);
296296
block++;
@@ -301,7 +301,7 @@ pgstat_heap(Relation rel, FunctionCallInfo fcinfo)
301301
while (block < nblocks)
302302
{
303303
buffer = ReadBuffer(rel, block);
304-
stat.free_space += PageGetFreeSpace((Page) BufferGetPage(buffer));
304+
stat.free_space += PageGetHeapFreeSpace((Page) BufferGetPage(buffer));
305305
ReleaseBuffer(buffer);
306306
block++;
307307
}

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.157 2007/09/05 18:10:47 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.158 2007/09/20 17:56:30 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -2565,6 +2565,29 @@
25652565
</entry>
25662566
</row>
25672567

2568+
<row>
2569+
<entry><structfield>indcheckxmin</structfield></entry>
2570+
<entry><type>bool</type></entry>
2571+
<entry></entry>
2572+
<entry>
2573+
If true, queries must not use the index until the <structfield>xmin</>
2574+
of this <structname>pg_index</> row is below their TransactionXmin
2575+
event horizon, because the table may contain broken HOT chains with
2576+
incompatible rows that they can see
2577+
</entry>
2578+
</row>
2579+
2580+
<row>
2581+
<entry><structfield>indisready</structfield></entry>
2582+
<entry><type>bool</type></entry>
2583+
<entry></entry>
2584+
<entry>
2585+
If true, the index is currently ready for inserts. False means the
2586+
index must be ignored by <command>INSERT</>/<command>UPDATE</>
2587+
operations
2588+
</entry>
2589+
</row>
2590+
25682591
<row>
25692592
<entry><structfield>indkey</structfield></entry>
25702593
<entry><type>int2vector</type></entry>

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 40 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.51 2007/06/28 00:02:37 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.52 2007/09/20 17:56:30 tgl Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -276,6 +276,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
276276
scans, number of index scans initiated (over all indexes
277277
belonging to the table), number of live rows fetched by index
278278
scans, numbers of row insertions, updates, and deletions,
279+
number of row updates that were HOT (i.e., no separate index update),
280+
numbers of live and dead rows,
279281
the last time the table was vacuumed manually,
280282
the last time it was vacuumed by the autovacuum daemon,
281283
the last time it was analyzed manually,
@@ -580,7 +582,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
580582
<entry><literal><function>pg_stat_get_tuples_updated</function>(<type>oid</type>)</literal></entry>
581583
<entry><type>bigint</type></entry>
582584
<entry>
583-
Number of rows updated in table
585+
Number of rows updated in table (includes HOT updates)
584586
</entry>
585587
</row>
586588

@@ -592,6 +594,30 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
592594
</entry>
593595
</row>
594596

597+
<row>
598+
<entry><literal><function>pg_stat_get_tuples_hot_updated</function>(<type>oid</type>)</literal></entry>
599+
<entry><type>bigint</type></entry>
600+
<entry>
601+
Number of rows HOT-updated in table
602+
</entry>
603+
</row>
604+
605+
<row>
606+
<entry><literal><function>pg_stat_get_live_tuples</function>(<type>oid</type>)</literal></entry>
607+
<entry><type>bigint</type></entry>
608+
<entry>
609+
Number of live rows in table
610+
</entry>
611+
</row>
612+
613+
<row>
614+
<entry><literal><function>pg_stat_get_dead_tuples</function>(<type>oid</type>)</literal></entry>
615+
<entry><type>bigint</type></entry>
616+
<entry>
617+
Number of dead rows in table
618+
</entry>
619+
</row>
620+
595621
<row>
596622
<entry><literal><function>pg_stat_get_blocks_fetched</function>(<type>oid</type>)</literal></entry>
597623
<entry><type>bigint</type></entry>
@@ -716,6 +742,18 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
716742
</entry>
717743
</row>
718744

745+
<row>
746+
<entry><literal><function>pg_stat_get_backend_xact_start</function>(<type>integer</type>)</literal></entry>
747+
<entry><type>timestamp with time zone</type></entry>
748+
<entry>
749+
The time at which the given server process' currently
750+
executing transaction was started, but only if the
751+
current user is a superuser or the same user as that of
752+
the session being queried (and
753+
<varname>stats_command_string</varname> is on)
754+
</entry>
755+
</row>
756+
719757
<row>
720758
<entry><literal><function>pg_stat_get_backend_start</function>(<type>integer</type>)</literal></entry>
721759
<entry><type>timestamp with time zone</type></entry>

‎doc/src/sgml/ref/create_index.sgml‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.64 2007/09/07 00:58:56 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.65 2007/09/20 17:56:30 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -329,7 +329,10 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] <replaceable class="parameter">name</re
329329
</para>
330330

331331
<para>
332-
If a problem arises during the second scan of the table, such as a
332+
In a concurrent index build, the index is actually entered into the
333+
system catalogs in one transaction, then the two table scans occur in a
334+
second and third transaction.
335+
If a problem arises while scanning the table, such as a
333336
uniqueness violation in a unique index, the <command>CREATE INDEX</>
334337
command will fail but leave behind an <quote>invalid</> index. This index
335338
will be ignored for querying purposes because it might be incomplete;

‎src/backend/access/gin/ginentrypage.c‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gin/ginentrypage.c,v 1.8 2007/09/12 22:10:25 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gin/ginentrypage.c,v 1.9 2007/09/20 17:56:30 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414

@@ -359,7 +359,7 @@ entryPlaceToPage(GinBtree btree, Buffer buf, OffsetNumber off, XLogRecData **prd
359359
*prdata = rdata;
360360
data.updateBlkno = entryPreparePage(btree, page, off);
361361

362-
placed = PageAddItem(page, (Item) btree->entry, IndexTupleSize(btree->entry), off, false);
362+
placed = PageAddItem(page, (Item) btree->entry, IndexTupleSize(btree->entry), off, false, false);
363363
if (placed != off)
364364
elog(ERROR, "failed to add item to index page in \"%s\"",
365365
RelationGetRelationName(btree->index));
@@ -488,7 +488,7 @@ entrySplitPage(GinBtree btree, Buffer lbuf, Buffer rbuf, OffsetNumber off, XLogR
488488
lsize += MAXALIGN(IndexTupleSize(itup)) + sizeof(ItemIdData);
489489
}
490490

491-
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false) == InvalidOffsetNumber)
491+
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false, false) == InvalidOffsetNumber)
492492
elog(ERROR, "failed to add item to index page in \"%s\"",
493493
RelationGetRelationName(btree->index));
494494
ptr += MAXALIGN(IndexTupleSize(itup));
@@ -563,11 +563,11 @@ entryFillRoot(GinBtree btree, Buffer root, Buffer lbuf, Buffer rbuf)
563563
page = BufferGetPage(root);
564564

565565
itup = ginPageGetLinkItup(lbuf);
566-
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false) == InvalidOffsetNumber)
566+
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false, false) == InvalidOffsetNumber)
567567
elog(ERROR, "failed to add item to index root page");
568568

569569
itup = ginPageGetLinkItup(rbuf);
570-
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false) == InvalidOffsetNumber)
570+
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false, false) == InvalidOffsetNumber)
571571
elog(ERROR, "failed to add item to index root page");
572572
}
573573

‎src/backend/access/gin/ginvacuum.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gin/ginvacuum.c,v 1.16 2007/09/12 22:10:25 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gin/ginvacuum.c,v 1.17 2007/09/20 17:56:30 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414

@@ -544,7 +544,7 @@ ginVacuumEntryPage(GinVacuumState *gvs, Buffer buffer, BlockNumber *roots, uint3
544544
itup = GinFormTuple(&gvs->ginstate, value, GinGetPosting(itup), newN);
545545
PageIndexTupleDelete(tmppage, i);
546546

547-
if (PageAddItem(tmppage, (Item) itup, IndexTupleSize(itup), i, false) != i)
547+
if (PageAddItem(tmppage, (Item) itup, IndexTupleSize(itup), i, false, false) != i)
548548
elog(ERROR, "failed to add item to index page in \"%s\"",
549549
RelationGetRelationName(gvs->index));
550550

‎src/backend/access/gin/ginxlog.c‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gin/ginxlog.c,v 1.8 2007/09/12 22:10:25 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gin/ginxlog.c,v 1.9 2007/09/20 17:56:30 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414
#include "postgres.h"
@@ -199,7 +199,7 @@ ginRedoInsert(XLogRecPtr lsn, XLogRecord *record)
199199

200200
itup = (IndexTuple) (XLogRecGetData(record) + sizeof(ginxlogInsert));
201201

202-
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), data->offset, false) == InvalidOffsetNumber)
202+
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), data->offset, false, false) == InvalidOffsetNumber)
203203
elog(ERROR, "failed to add item to index page in %u/%u/%u",
204204
data->node.spcNode, data->node.dbNode, data->node.relNode);
205205

@@ -281,15 +281,15 @@ ginRedoSplit(XLogRecPtr lsn, XLogRecord *record)
281281

282282
for (i = 0; i < data->separator; i++)
283283
{
284-
if (PageAddItem(lpage, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false) == InvalidOffsetNumber)
284+
if (PageAddItem(lpage, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false, false) == InvalidOffsetNumber)
285285
elog(ERROR, "failed to add item to index page in %u/%u/%u",
286286
data->node.spcNode, data->node.dbNode, data->node.relNode);
287287
itup = (IndexTuple) (((char *) itup) + MAXALIGN(IndexTupleSize(itup)));
288288
}
289289

290290
for (i = data->separator; i < data->nitem; i++)
291291
{
292-
if (PageAddItem(rpage, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false) == InvalidOffsetNumber)
292+
if (PageAddItem(rpage, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false, false) == InvalidOffsetNumber)
293293
elog(ERROR, "failed to add item to index page in %u/%u/%u",
294294
data->node.spcNode, data->node.dbNode, data->node.relNode);
295295
itup = (IndexTuple) (((char *) itup) + MAXALIGN(IndexTupleSize(itup)));
@@ -375,7 +375,7 @@ ginRedoVacuumPage(XLogRecPtr lsn, XLogRecord *record)
375375

376376
for (i = 0; i < data->nitem; i++)
377377
{
378-
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false) == InvalidOffsetNumber)
378+
if (PageAddItem(page, (Item) itup, IndexTupleSize(itup), InvalidOffsetNumber, false, false) == InvalidOffsetNumber)
379379
elog(ERROR, "failed to add item to index page in %u/%u/%u",
380380
data->node.spcNode, data->node.dbNode, data->node.relNode);
381381
itup = (IndexTuple) (((char *) itup) + MAXALIGN(IndexTupleSize(itup)));

‎src/backend/access/gist/gist.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gist.c,v 1.146 2007/09/12 22:10:25 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gist.c,v 1.147 2007/09/20 17:56:30 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -366,7 +366,7 @@ gistplacetopage(GISTInsertState *state, GISTSTATE *giststate)
366366
data = (char *) (ptr->list);
367367
for (i = 0; i < ptr->block.num; i++)
368368
{
369-
if (PageAddItem(ptr->page, (Item) data, IndexTupleSize((IndexTuple) data), i + FirstOffsetNumber, false) == InvalidOffsetNumber)
369+
if (PageAddItem(ptr->page, (Item) data, IndexTupleSize((IndexTuple) data), i + FirstOffsetNumber, false, false) == InvalidOffsetNumber)
370370
elog(ERROR, "failed to add item to index page in \"%s\"", RelationGetRelationName(state->r));
371371
data += IndexTupleSize((IndexTuple) data);
372372
}

‎src/backend/access/gist/gistutil.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gistutil.c,v 1.23 2007/09/12 22:10:25 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistutil.c,v 1.24 2007/09/20 17:56:30 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414
#include "postgres.h"
@@ -42,7 +42,7 @@ gistfillbuffer(Relation r, Page page, IndexTuple *itup,
4242
for (i = 0; i < len; i++)
4343
{
4444
l = PageAddItem(page, (Item) itup[i], IndexTupleSize(itup[i]),
45-
off, false);
45+
off, false, false);
4646
if (l == InvalidOffsetNumber)
4747
elog(ERROR, "failed to add item to index page in \"%s\"",
4848
RelationGetRelationName(r));

‎src/backend/access/gist/gistvacuum.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gistvacuum.c,v 1.31 2007/09/12 22:10:25 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistvacuum.c,v 1.32 2007/09/20 17:56:30 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -201,7 +201,7 @@ vacuumSplitPage(GistVacuum *gv, Page tempPage, Buffer buffer, IndexTuple *addon,
201201
data = (char *) (ptr->list);
202202
for (i = 0; i < ptr->block.num; i++)
203203
{
204-
if (PageAddItem(ptr->page, (Item) data, IndexTupleSize((IndexTuple) data), i + FirstOffsetNumber, false) == InvalidOffsetNumber)
204+
if (PageAddItem(ptr->page, (Item) data, IndexTupleSize((IndexTuple) data), i + FirstOffsetNumber, false, false) == InvalidOffsetNumber)
205205
elog(ERROR, "failed to add item to index page in \"%s\"", RelationGetRelationName(gv->index));
206206
data += IndexTupleSize((IndexTuple) data);
207207
}

0 commit comments

Comments
 (0)
0