Hi David,
Rebased patch is attached.
On 14.03.2016 15:09, David Steele wrote:
Hi Konstantin,
On 2/3/16 11:47 AM, Konstantin Knizhnik wrote:
Attached please find patch for "ALTER INDEX ... WHERE ..." clause.
It is now able to handle all three possible situations:
1. Making index partial (add WHERE condition to the ordinary index)
2. Extend partial index range (less restricted index predicate)
3. Arbitrary change of partial index predicate
In case 2) new records are added to the index.
In other two cases index is completely reconstructed.
This patch includes src/bin/insbench utility for testing insert
performance. It can be easily excluded from the patch to reduce it size.
Also it is better to apply this patch together with "index-only scans
with partial indexes" patch:
This patch no longer applies on master:
$ git apply ../other/alter-index.patch
../other/alter-index.patch:27: trailing whitespace.
static void
../other/alter-index.patch:62: indent with spaces.
SPIPlanPtr plan;
../other/alter-index.patch:63: indent with spaces.
Portal portal;
../other/alter-index.patch:90: trailing whitespace.
../other/alter-index.patch:99: trailing whitespace.
error: patch failed: src/test/regress/expected/aggregates.out:831
error: src/test/regress/expected/aggregates.out: patch does not apply
Please provide a new patch for review. Meanwhile I am marking this
"waiting on author".
Thanks,
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index ee3e3de..ab042ed 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -27,6 +27,7 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET
ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
ALTER INDEX ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable> [ OWNED BY <replaceable class="PARAMETER">role_name</replaceable> [, ... ] ]
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable> [ NOWAIT ]
+ALTER INDEX <replaceable class="PARAMETER">name</replaceable> WHERE <replaceable class="parameter">predicate</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -109,6 +110,18 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>WHERE <replaceable class="parameter">predicate</replaceable></literal></term>
+ <listitem>
+ <para>
+ Add or change predicate of partial index. Extending partial index predicate allows to implement batch update of index and so
+ increase insert speed. New records (not matching index predicate) can be added to the table at maximal speed without affecting indexes.
+ Later, in background, indexes can be refreshed using <literal>ALTER INDEX ... WHERE ...</literal> clause.
+ See <xref linkend="indexes-partial"> for more discussion.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 13b04e6..a63de2a 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -34,6 +34,7 @@
#include "commands/tablespace.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
+#include "funcapi.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/planner.h"
@@ -52,6 +53,9 @@
#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
+#include "utils/ruleutils.h"
+#include "executor/executor.h"
+#include "executor/spi.h"
/* non-export function prototypes */
@@ -280,6 +284,160 @@ CheckIndexCompatible(Oid oldId,
return ret;
}
+static void
+UpdateIndex(Oid indexRelationId, Node* whereClause)
+{
+ Datum values[Natts_pg_index];
+ bool isnull[Natts_pg_index];
+ HeapTuple oldTuple;
+ HeapTuple newTuple;
+ Relation pg_index;
+
+ pg_index = heap_open(IndexRelationId, RowExclusiveLock);
+ oldTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexRelationId));
+ if (!HeapTupleIsValid(oldTuple))
+ elog(ERROR, "cache lookup failed for index %u", indexRelationId);
+
+ heap_deform_tuple(oldTuple, RelationGetDescr(pg_index), values, isnull);
+ values[Anum_pg_index_indpred - 1] = CStringGetTextDatum(nodeToString(whereClause));
+ isnull[Anum_pg_index_indpred - 1] = false;
+ newTuple = heap_form_tuple(RelationGetDescr(pg_index), values, isnull);
+ simple_heap_update(pg_index, &oldTuple->t_self, newTuple);
+ CatalogUpdateIndexes(pg_index, newTuple);
+ heap_freetuple(newTuple);
+ heap_freetuple(oldTuple);
+ heap_close(pg_index, NoLock);
+}
+
+void
+AlterIndex(Oid indexRelationId, IndexStmt *stmt)
+{
+ char* select;
+ Oid heapRelationId;
+ IndexUniqueCheck checkUnique;
+ Datum values[INDEX_MAX_KEYS];
+ bool isnull[INDEX_MAX_KEYS];
+ Relation heapRelation;
+ Relation indexRelation;
+ SPIPlanPtr plan;
+ Portal portal;
+ HeapTuple tuple;
+ TupleTableSlot *slot;
+ ItemPointer tupleid;
+ IndexInfo *indexInfo;
+ EState *estate;
+ Oid namespaceId;
+ List* deparseCtx;
+ char* oldIndexPredicate;
+ char* newIndexPredicate;
+ char* relationName;
+
+ Assert(stmt->whereClause);
+ CheckPredicate((Expr *) stmt->whereClause);
+
+ /* Open and lock the parent heap relation */
+ heapRelationId = IndexGetRelation(indexRelationId, false);
+ heapRelation = heap_open(heapRelationId, AccessShareLock);
+
+ /* Open the target index relation */
+ /* indexRelation = index_open(indexRelationId, RowExclusiveLock); */
+ indexRelation = index_open(indexRelationId, ShareUpdateExclusiveLock);
+ /* indexRelation = index_open(indexRelationId, AccessShareLock); */
+ namespaceId = RelationGetNamespace(indexRelation);
+
+ indexInfo = BuildIndexInfo(indexRelation);
+ Assert(!indexInfo->ii_ExclusionOps);
+
+ /*
+ * Generate the constraint and default execution states
+ */
+ estate = CreateExecutorState();
+
+ checkUnique = indexRelation->rd_index->indisunique ? UNIQUE_CHECK_YES : UNIQUE_CHECK_NO;
+
+ slot = MakeSingleTupleTableSlot(RelationGetDescr(heapRelation));
+
+ deparseCtx = deparse_context_for(RelationGetRelationName(heapRelation), heapRelationId);
+ relationName = quote_qualified_identifier(get_namespace_name(namespaceId),
+ get_rel_name(heapRelationId)),
+ newIndexPredicate = deparse_expression(stmt->whereClause, deparseCtx, false, false);
+ oldIndexPredicate = indexInfo->ii_Predicate
+ ? deparse_expression((Node*)make_ands_explicit(indexInfo->ii_Predicate), deparseCtx, false, false)
+ : "true";
+
+ SPI_connect();
+
+ select = psprintf("select * from %s where %s and not (%s) limit 1",
+ relationName, oldIndexPredicate, newIndexPredicate);
+ if (SPI_execute(select, true, 1) != SPI_OK_SELECT)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("Failed to execute statement %s", select)));
+ }
+ if (SPI_processed) {
+ /* There is no way in Postgres to exclude records from index, so we have to completelty rebuild index in this case */
+ bool relpersistence = indexRelation->rd_rel->relpersistence;
+ index_close(indexRelation, NoLock);
+ indexRelation->rd_indpred = make_ands_implicit((Expr *) stmt->whereClause);
+ indexRelation = NULL;
+ UpdateIndex(indexRelationId, stmt->whereClause);
+ reindex_index(indexRelationId, false, relpersistence, 0);
+ } else {
+ select = psprintf("select * from %s where %s and not (%s)",
+ relationName, newIndexPredicate, oldIndexPredicate);
+ plan = SPI_prepare(select, 0, NULL);
+ if (plan == NULL) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("Failed to preapre statement %s", select)));
+ }
+ portal = SPI_cursor_open(NULL, plan, NULL, NULL, true);
+ if (portal == NULL) {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_STATE),
+ errmsg("Failed to open cursor for %s", select)));
+ }
+ while (true)
+ {
+ SPI_cursor_fetch(portal, true, 1);
+ if (!SPI_processed) {
+ break;
+ }
+ tuple = SPI_tuptable->vals[0];
+ tupleid = &tuple->t_data->t_ctid;
+ ExecStoreTuple(tuple, slot, InvalidBuffer, false);
+
+ FormIndexDatum(indexInfo,
+ slot,
+ estate,
+ values,
+ isnull);
+ index_insert(indexRelation, /* index relation */
+ values, /* array of index Datums */
+ isnull, /* null flags */
+ tupleid, /* tid of heap tuple */
+ heapRelation, /* heap relation */
+ checkUnique); /* type of uniqueness check to do */
+
+ SPI_freetuple(tuple);
+ SPI_freetuptable(SPI_tuptable);
+ }
+ SPI_cursor_close(portal);
+
+ UpdateIndex(indexRelationId, stmt->whereClause);
+ }
+ SPI_finish();
+
+ ExecDropSingleTupleTableSlot(slot);
+ FreeExecutorState(estate);
+
+ heap_close(heapRelation, NoLock);
+ if (indexRelation) {
+ index_close(indexRelation, NoLock);
+ }
+}
+
/*
* DefineIndex
* Creates a new index.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index df7c2fa..7cd8e7f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3129,6 +3129,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(transformed);
COPY_SCALAR_FIELD(concurrent);
COPY_SCALAR_FIELD(if_not_exists);
+ COPY_SCALAR_FIELD(is_alter);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b9c3959..3a7aa51 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1263,6 +1263,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(transformed);
COMPARE_SCALAR_FIELD(concurrent);
COMPARE_SCALAR_FIELD(if_not_exists);
+ COMPARE_SCALAR_FIELD(is_alter);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index eb0fc1e..f4a0c42 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2419,6 +2419,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node)
WRITE_BOOL_FIELD(transformed);
WRITE_BOOL_FIELD(concurrent);
WRITE_BOOL_FIELD(if_not_exists);
+ WRITE_BOOL_FIELD(is_alter);
}
static void
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b9aeb31..bb1a103 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1801,6 +1801,15 @@ AlterTableStmt:
n->nowait = $13;
$$ = (Node *)n;
}
+ | ALTER INDEX qualified_name WHERE a_expr
+ {
+ IndexStmt* n = makeNode(IndexStmt);
+ n->relation = $3;
+ n->whereClause = $5;
+ n->is_alter = true;
+ $$ = (Node *)n;
+ }
+
| ALTER INDEX qualified_name alter_table_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index dc431c7..9c6cd6f 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2066,6 +2066,10 @@ transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString)
* to its fields without qualification. Caller is responsible for locking
* relation, but we still need to open it.
*/
+ if (stmt->is_alter)
+ {
+ relid = IndexGetRelation(relid, false);
+ }
rel = relation_open(relid, NoLock);
rte = addRangeTableEntryForRelation(pstate, rel, NULL, false, true);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 045f7f0..1732457 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1244,7 +1244,7 @@ ProcessUtilitySlow(Node *parsetree,
* eventually be needed here, so the lockmode calculation
* needs to match what DefineIndex() does.
*/
- lockmode = stmt->concurrent ? ShareUpdateExclusiveLock
+ lockmode = stmt->is_alter || stmt->concurrent ? ShareUpdateExclusiveLock
: ShareLock;
relid =
RangeVarGetRelidExtended(stmt->relation, lockmode,
@@ -1257,22 +1257,29 @@ ProcessUtilitySlow(Node *parsetree,
/* ... and do it */
EventTriggerAlterTableStart(parsetree);
- address =
- DefineIndex(relid, /* OID of heap relation */
- stmt,
- InvalidOid, /* no predefined OID */
- false, /* is_alter_table */
- true, /* check_rights */
- false, /* skip_build */
- false); /* quiet */
+ if (stmt->is_alter)
+ {
+ AlterIndex(relid, stmt);
+ }
+ else
+ {
+ address =
+ DefineIndex(relid, /* OID of heap relation */
+ stmt,
+ InvalidOid, /* no predefined OID */
+ false, /* is_alter_table */
+ true, /* check_rights */
+ false, /* skip_build */
+ false); /* quiet */
- /*
- * Add the CREATE INDEX node itself to stash right away;
- * if there were any commands stashed in the ALTER TABLE
- * code, we need them to appear after this one.
- */
- EventTriggerCollectSimpleCommand(address, secondaryObject,
- parsetree);
+ /*
+ * Add the CREATE INDEX node itself to stash right away;
+ * if there were any commands stashed in the ALTER TABLE
+ * code, we need them to appear after this one.
+ */
+ EventTriggerCollectSimpleCommand(address, secondaryObject,
+ parsetree);
+ }
commandCollected = true;
EventTriggerAlterTableEnd();
}
diff --git a/src/bin/insbench/insbench.cpp b/src/bin/insbench/insbench.cpp
new file mode 100644
index 0000000..5d58418
--- /dev/null
+++ b/src/bin/insbench/insbench.cpp
@@ -0,0 +1,323 @@
+#include <time.h>
+#include <stdio.h>
+#include <stdarg.h>
+#include <stdlib.h>
+#include <inttypes.h>
+#include <sys/time.h>
+#include <pthread.h>
+#include <unistd.h>
+
+#include <string>
+#include <vector>
+
+#include <pqxx/connection>
+#include <pqxx/transaction>
+#include <pqxx/nontransaction>
+#include <pqxx/pipeline>
+#include <pqxx/tablewriter>
+#include <pqxx/version>
+
+using namespace std;
+using namespace pqxx;
+
+typedef void* (*thread_proc_t)(void*);
+
+struct thread
+{
+ pthread_t t;
+
+ void start(thread_proc_t proc) {
+ pthread_create(&t, NULL, proc, this);
+ }
+
+ void wait() {
+ pthread_join(t, NULL);
+ }
+};
+
+struct config
+{
+ int indexUpdateInterval;
+ int nInserters;
+ int nIndexes;
+ int nIterations;
+ int transactionSize;
+ int initialSize;
+ bool useSystemTime;
+ bool noPK;
+ bool useCopy;
+ string connection;
+
+ config() {
+ initialSize = 1000000;
+ indexUpdateInterval = 0;
+ nInserters = 1;
+ nIndexes = 8;
+ nIterations = 10000;
+ transactionSize = 100;
+ useSystemTime = false;
+ noPK = false;
+ useCopy = false;
+ }
+};
+
+config cfg;
+bool running;
+int nIndexUpdates;
+time_t maxIndexUpdateTime;
+time_t totalIndexUpdateTime;
+time_t currTimestamp;
+
+#define USEC 1000000
+
+static time_t getCurrentTime()
+{
+ struct timeval tv;
+ gettimeofday(&tv, NULL);
+ return (time_t)tv.tv_sec*USEC + tv.tv_usec;
+}
+
+
+void exec(transaction_base& txn, char const* sql, ...)
+{
+ va_list args;
+ va_start(args, sql);
+ char buf[1024];
+ vsprintf(buf, sql, args);
+ va_end(args);
+ txn.exec(buf);
+}
+
+void* inserter(void* arg)
+{
+ connection con(cfg.connection);
+ if (cfg.useSystemTime)
+ {
+#if PQXX_VERSION_MAJOR >= 4
+ con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)");
+#else
+ con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint");
+#endif
+ } else {
+ con.prepare("insert", "insert into t (select generate_series($1::integer,$2::integer),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000))");
+ }
+ time_t curr = currTimestamp;
+
+ for (int i = 0; i < cfg.nIterations; i++)
+ {
+ work txn(con);
+ if (cfg.useSystemTime)
+ {
+ if (cfg.useCopy)
+ {
+ tablewriter writer(txn,"t");
+ vector<int64_t> row(9);
+ for (int j = 0; j < cfg.transactionSize; j++)
+ {
+ row[0] = getCurrentTime();
+ for (int c = 1; c <= 8; c++) {
+ row[c] = random();
+ }
+ writer << row;
+ }
+ writer.complete();
+ } else {
+ for (int j = 0; j < cfg.transactionSize; j++)
+ {
+ txn.prepared("insert")(getCurrentTime())(random())(random())(random())(random())(random())(random())(random())(random()).exec();
+ }
+ }
+ } else {
+ txn.prepared("insert")(curr)(curr+cfg.transactionSize-1).exec();
+ curr += cfg.transactionSize;
+ currTimestamp = curr;
+ }
+ txn.commit();
+ }
+ return NULL;
+}
+
+void* indexUpdater(void* arg)
+{
+ connection con(cfg.connection);
+ while (running) {
+ sleep(cfg.indexUpdateInterval);
+ printf("Alter indexes\n");
+ time_t now = getCurrentTime();
+ time_t limit = cfg.useSystemTime ? now : currTimestamp;
+ {
+ work txn(con);
+ for (int i = 0; i < cfg.nIndexes; i++) {
+ exec(txn, "alter index idx%d where pk<%lu", i, limit);
+ }
+ txn.commit();
+ }
+ printf("End alter indexes\n");
+ nIndexUpdates += 1;
+ time_t elapsed = getCurrentTime() - now;
+ totalIndexUpdateTime += elapsed;
+ if (elapsed > maxIndexUpdateTime) {
+ maxIndexUpdateTime = elapsed;
+ }
+ }
+ return NULL;
+}
+
+void initializeDatabase()
+{
+ connection con(cfg.connection);
+ work txn(con);
+ time_t now = getCurrentTime();
+ exec(txn, "drop table if exists t");
+ exec(txn, "create table t (pk bigint, k1 bigint, k2 bigint, k3 bigint, k4 bigint, k5 bigint, k6 bigint, k7 bigint, k8 bigint)");
+
+ if (cfg.initialSize)
+ {
+ if (cfg.useSystemTime)
+ {
+#if PQXX_VERSION_MAJOR >= 4
+ con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)");
+#else
+ con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint");
+#endif
+ } else {
+ con.prepare("insert", "insert into t (select generate_series($1::integer,$2::integer),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000))");
+ }
+ if (cfg.useSystemTime)
+ {
+ if (cfg.useCopy) {
+ tablewriter writer(txn,"t");
+ vector<int64_t> row(9);
+ for (int i = 0; i < cfg.initialSize; i++)
+ {
+ row[0] = getCurrentTime();
+ for (int c = 1; c <= 8; c++) {
+ row[c] = random();
+ }
+ writer << row;
+ }
+ writer.complete();
+ } else {
+ for (int i = 0; i < cfg.initialSize; i++)
+ {
+ txn.prepared("insert")(getCurrentTime())(random())(random())(random())(random())(random())(random())(random())(random()).exec();
+ }
+ }
+ } else {
+ txn.prepared("insert")(cfg.initialSize)(cfg.initialSize-1).exec();
+ currTimestamp = cfg.initialSize;
+ }
+ }
+ if (!cfg.noPK) {
+ exec(txn, "create index pk on t(pk)");
+ }
+ for (int i = 0; i < cfg.nIndexes; i++) {
+ if (cfg.indexUpdateInterval == 0) {
+ exec(txn, "create index idx%d on t(k%d)", i, i+1);
+ } else if (cfg.useSystemTime) {
+ exec(txn, "create index idx%d on t(k%d) where pk<%ld", i, i+1, now);
+ } else {
+ exec(txn, "create index idx%d on t(k%d) where pk<%ld", i, i+1, currTimestamp);
+ }
+ }
+ txn.commit();
+ {
+ nontransaction txn(con);
+ txn.exec("vacuum analyze");
+ sleep(2);
+ }
+ printf("Database intialized\n");
+}
+
+
+int main (int argc, char* argv[])
+{
+ if (argc == 1){
+ printf("Use -h to show usage options\n");
+ return 1;
+ }
+
+ for (int i = 1; i < argc; i++) {
+ if (argv[i][0] == '-') {
+ switch (argv[i][1]) {
+ case 't':
+ cfg.transactionSize = atoi(argv[++i]);
+ continue;
+ case 'w':
+ cfg.nInserters = atoi(argv[++i]);
+ continue;
+ case 'u':
+ cfg.indexUpdateInterval = atoi(argv[++i]);
+ continue;
+ case 'n':
+ cfg.nIterations = atoi(argv[++i]);
+ continue;
+ case 'x':
+ cfg.nIndexes = atoi(argv[++i]);
+ continue;
+ case 'i':
+ cfg.initialSize = atoi(argv[++i]);
+ continue;
+ case 'c':
+ cfg.connection = string(argv[++i]);
+ continue;
+ case 'q':
+ cfg.useSystemTime = true;
+ continue;
+ case 'p':
+ cfg.noPK = true;
+ continue;
+ case 'C':
+ cfg.useCopy = true;
+ continue;
+ }
+ }
+ printf("Options:\n"
+ "\t-t N\ttransaction size (100)\n"
+ "\t-w N\tnumber of inserters (1)\n"
+ "\t-u N\tindex update interval (0)\n"
+ "\t-n N\tnumber of iterations (10000)\n"
+ "\t-x N\tnumber of indexes (8)\n"
+ "\t-i N\tinitial table size (1000000)\n"
+ "\t-q\tuse system time and libpq\n"
+ "\t-p\tno primary key\n"
+ "\t-C\tuse COPY command\n"
+ "\t-c STR\tdatabase connection string\n");
+ return 1;
+ }
+
+ initializeDatabase();
+
+ time_t start = getCurrentTime();
+ running = true;
+
+ vector<thread> inserters(cfg.nInserters);
+ thread bgw;
+ for (int i = 0; i < cfg.nInserters; i++) {
+ inserters[i].start(inserter);
+ }
+ if (cfg.indexUpdateInterval != 0) {
+ bgw.start(indexUpdater);
+ }
+ for (int i = 0; i < cfg.nInserters; i++) {
+ inserters[i].wait();
+ }
+ time_t elapsed = getCurrentTime() - start;
+
+ running = false;
+ bgw.wait();
+
+
+ printf(
+ "{\"tps\":%f, \"index_updates\":%d, \"max_update_time\":%ld, \"avg_update_time\":%f,"
+ " \"inserters\":%d, \"indexes\":%d, \"transaction_size\":%d, \"iterations\":%d}\n",
+ (double)cfg.nInserters*cfg.transactionSize*cfg.nIterations*USEC/elapsed,
+ nIndexUpdates,
+ maxIndexUpdateTime,
+ (double)totalIndexUpdateTime/nIndexUpdates,
+ cfg.nInserters,
+ cfg.nIndexes,
+ cfg.transactionSize,
+ cfg.nIterations);
+ return 0;
+}
diff --git a/src/bin/insbench/makefile b/src/bin/insbench/makefile
new file mode 100644
index 0000000..a5305ae
--- /dev/null
+++ b/src/bin/insbench/makefile
@@ -0,0 +1,10 @@
+CXX=g++
+CXXFLAGS=-g -Wall -O2 -pthread
+
+all: insbench
+
+insbench: insbench.cpp
+ $(CXX) $(CXXFLAGS) -o insbench insbench.cpp -lpqxx
+
+clean:
+ rm -f insbench
diff --git a/src/bin/insbench/run.sh b/src/bin/insbench/run.sh
new file mode 100644
index 0000000..86ba823
--- /dev/null
+++ b/src/bin/insbench/run.sh
@@ -0,0 +1,8 @@
+echo Insert with 1 index
+./insbench -c "dbname=postgres host=localhost port=5432 sslmode=disable" -q -C -x 0
+echo Insert with 9 indexex
+./insbench -c "dbname=postgres host=localhost port=5432 sslmode=disable" -q -C -x 8
+echo Insert with 9 concurrently update partial indexes
+./insbench -c "dbname=postgres host=localhost port=5432 sslmode=disable" -q -C -x 8 -u 1
+echo Insert with 9 frozen partial indexes
+./insbench -c "dbname=postgres host=localhost port=5432 sslmode=disable" -q -C -x 8 -u 100
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 54f67e9..23c9d8e 100644
--- a/src/include/commands/defrem.h
+++ b/src/include/commands/defrem.h
@@ -42,6 +42,7 @@ extern bool CheckIndexCompatible(Oid oldId,
List *attributeList,
List *exclusionOpNames);
extern Oid GetDefaultOpClass(Oid type_id, Oid am_id);
+extern void AlterIndex(Oid relationId, IndexStmt *stmt);
/* commands/functioncmds.c */
extern ObjectAddress CreateFunction(CreateFunctionStmt *stmt, const char *queryString);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2fd0629..4137dcd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2440,6 +2440,7 @@ typedef struct IndexStmt
bool transformed; /* true when transformIndexStmt is finished */
bool concurrent; /* should this be a concurrent index build? */
bool if_not_exists; /* just do nothing if index already exists? */
+ bool is_alter; /* is alter index statement */
} IndexStmt;
/* ----------------------
diff --git a/src/test/regress/expected/alter_index_with.out b/src/test/regress/expected/alter_index_with.out
new file mode 100644
index 0000000..3f46bc5
--- /dev/null
+++ b/src/test/regress/expected/alter_index_with.out
@@ -0,0 +1,132 @@
+create table tmptab (pk integer primary key, sk integer);
+-- insert enough records to make psotgresql optimizer use indexes
+insert into tmptab values (generate_series(1, 10000), generate_series(1, 10000));
+vacuum analyze;
+-- create normal index
+create index idx on tmptab(sk);
+-- just normal index search
+select * from tmptab where sk = 100;
+ pk | sk
+-----+-----
+ 100 | 100
+(1 row)
+
+-- make index partial
+alter index idx where pk < 1000;
+-- select using exact partial index range
+select * from tmptab where sk = 100 and pk < 1000;
+ pk | sk
+-----+-----
+ 100 | 100
+(1 row)
+
+explain select * from tmptab where sk = 100 and pk < 1000;
+ QUERY PLAN
+------------------------------------------------------------------
+ Index Scan using idx on tmptab (cost=0.28..8.29 rows=1 width=8)
+ Index Cond: (sk = 100)
+(2 rows)
+
+-- select using subset of partial index range
+select * from tmptab where sk = 100 and pk < 200;
+ pk | sk
+-----+-----
+ 100 | 100
+(1 row)
+
+explain select * from tmptab where sk = 100 and pk < 200;
+ QUERY PLAN
+------------------------------------------------------------------
+ Index Scan using idx on tmptab (cost=0.28..8.29 rows=1 width=8)
+ Index Cond: (sk = 100)
+ Filter: (pk < 200)
+(3 rows)
+
+-- select outside partial index range
+select * from tmptab where sk = 100 and pk > 1000;
+ pk | sk
+----+----
+(0 rows)
+
+explain select * from tmptab where sk = 100 and pk > 1000;
+ QUERY PLAN
+--------------------------------------------------------
+ Seq Scan on tmptab (cost=0.00..195.00 rows=1 width=8)
+ Filter: ((pk > 1000) AND (sk = 100))
+(2 rows)
+
+-- select without partial index range
+select * from tmptab where sk = 100;
+ pk | sk
+-----+-----
+ 100 | 100
+(1 row)
+
+explain select * from tmptab where sk = 100;
+ QUERY PLAN
+--------------------------------------------------------
+ Seq Scan on tmptab (cost=0.00..170.00 rows=1 width=8)
+ Filter: (sk = 100)
+(2 rows)
+
+-- extend partial index range
+alter index idx where pk < 10000;
+-- select using exact partial index range
+select * from tmptab where sk = 1000 and pk < 10000;
+ pk | sk
+------+------
+ 1000 | 1000
+(1 row)
+
+explain select * from tmptab where sk = 1000 and pk < 10000;
+ QUERY PLAN
+------------------------------------------------------------------
+ Index Scan using idx on tmptab (cost=0.28..8.30 rows=1 width=8)
+ Index Cond: (sk = 1000)
+(2 rows)
+
+-- calculating aggregate within exact partial index range
+select count(*) from tmptab where sk < 1000 and pk < 10000;
+ count
+-------
+ 999
+(1 row)
+
+explain select count(*) from tmptab where sk < 1000 and pk < 10000;
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Aggregate (cost=50.78..50.79 rows=1 width=8)
+ -> Index Scan using idx on tmptab (cost=0.28..48.28 rows=1000 width=0)
+ Index Cond: (sk < 1000)
+(3 rows)
+
+-- reducing partial idex predicate
+alter index idx where pk < 9000;
+-- select using new exact partial index range and key value belonging to old range
+select * from tmptab where sk = 9000 and pk < 9000;
+ pk | sk
+----+----
+(0 rows)
+
+explain select * from tmptab where sk = 9000 and pk < 9000;
+ QUERY PLAN
+------------------------------------------------------------------
+ Index Scan using idx on tmptab (cost=0.29..8.30 rows=1 width=8)
+ Index Cond: (sk = 9000)
+(2 rows)
+
+-- select using exact partial index range
+select * from tmptab where sk = 900 and pk < 9000;
+ pk | sk
+-----+-----
+ 900 | 900
+(1 row)
+
+explain select * from tmptab where sk = 900 and pk < 9000;
+ QUERY PLAN
+------------------------------------------------------------------
+ Index Scan using idx on tmptab (cost=0.29..8.30 rows=1 width=8)
+ Index Cond: (sk = 900)
+(2 rows)
+
+drop table tmptab;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index bec0316..09c669a 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -103,7 +103,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table alter_index_with sequence polymorphism rowtypes returning largeobject with xml
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 7e9b319..4867bcc 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -153,6 +153,7 @@ test: without_oid
test: conversion
test: truncate
test: alter_table
+test: alter_index_with
test: sequence
test: polymorphism
test: rowtypes
diff --git a/src/test/regress/sql/alter_index_with.sql b/src/test/regress/sql/alter_index_with.sql
new file mode 100644
index 0000000..ce92ce0
--- /dev/null
+++ b/src/test/regress/sql/alter_index_with.sql
@@ -0,0 +1,55 @@
+create table tmptab (pk integer primary key, sk integer);
+
+-- insert enough records to make psotgresql optimizer use indexes
+insert into tmptab values (generate_series(1, 10000), generate_series(1, 10000));
+
+vacuum analyze;
+
+-- create normal index
+create index idx on tmptab(sk);
+
+-- just normal index search
+select * from tmptab where sk = 100;
+
+-- make index partial
+alter index idx where pk < 1000;
+
+-- select using exact partial index range
+select * from tmptab where sk = 100 and pk < 1000;
+explain select * from tmptab where sk = 100 and pk < 1000;
+
+-- select using subset of partial index range
+select * from tmptab where sk = 100 and pk < 200;
+explain select * from tmptab where sk = 100 and pk < 200;
+
+-- select outside partial index range
+select * from tmptab where sk = 100 and pk > 1000;
+explain select * from tmptab where sk = 100 and pk > 1000;
+
+-- select without partial index range
+select * from tmptab where sk = 100;
+explain select * from tmptab where sk = 100;
+
+-- extend partial index range
+alter index idx where pk < 10000;
+
+-- select using exact partial index range
+select * from tmptab where sk = 1000 and pk < 10000;
+explain select * from tmptab where sk = 1000 and pk < 10000;
+
+-- calculating aggregate within exact partial index range
+select count(*) from tmptab where sk < 1000 and pk < 10000;
+explain select count(*) from tmptab where sk < 1000 and pk < 10000;
+
+-- reducing partial idex predicate
+alter index idx where pk < 9000;
+
+-- select using new exact partial index range and key value belonging to old range
+select * from tmptab where sk = 9000 and pk < 9000;
+explain select * from tmptab where sk = 9000 and pk < 9000;
+
+-- select using exact partial index range
+select * from tmptab where sk = 900 and pk < 9000;
+explain select * from tmptab where sk = 900 and pk < 9000;
+
+drop table tmptab;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
