diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 303436c89d..0695e66399 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -21,7 +21,17 @@ PostgreSQL documentation -REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name +REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name + +where option can be one of: + + FILTER = filter_option + VERBOSE + +and filter_option can only be: + + COLLATION + @@ -165,6 +175,20 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURR + + FILTER + + + When a filter is used, PostgreSQL will only + rebuild a subset of indexes depending on the filters used. For now, the + only available filter is COLLATION, which will + discard indexes whose ordering does not depend on a collation. Note that + the FILTER option is not compatible with REINDEX + SCHEMA. + + + + VERBOSE diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index bb60b23093..e0be50754b 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -3524,7 +3524,7 @@ reindex_relation(Oid relid, int flags, int options) * relcache to get this with a sequential scan if ignoring system * indexes.) */ - indexIds = RelationGetIndexList(rel); + indexIds = RelationGetIndexListFiltered(rel, options); PG_TRY(); { diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 6fa5738bbf..4c3bb49230 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2790,7 +2790,7 @@ ReindexRelationConcurrently(Oid relationOid, int options) heapRelation = table_open(relationOid, ShareUpdateExclusiveLock); /* Add all the valid indexes of relation to list */ - foreach(lc, RelationGetIndexList(heapRelation)) + foreach(lc, RelationGetIndexListFiltered(heapRelation, options)) { Oid cellOid = lfirst_oid(lc); Relation indexRelation = index_open(cellOid, @@ -2836,7 +2836,8 @@ ReindexRelationConcurrently(Oid relationOid, int options) MemoryContextSwitchTo(oldcontext); - foreach(lc2, RelationGetIndexList(toastRelation)) + foreach(lc2, RelationGetIndexListFiltered(toastRelation, + options)) { Oid cellOid = lfirst_oid(lc2); Relation indexRelation = index_open(cellOid, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 208b4a1f28..a1cb184ee2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -510,6 +510,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type reindex_target_type reindex_target_multitable %type reindex_option_list reindex_option_elem +%type reindex_filter reindex_filter_elem %type copy_generic_opt_arg copy_generic_opt_arg_list_item %type copy_generic_opt_elem @@ -8387,7 +8388,14 @@ reindex_option_list: ; reindex_option_elem: VERBOSE { $$ = REINDEXOPT_VERBOSE; } + | reindex_filter { $$ = $1; } ; +reindex_filter: + FILTER '=' reindex_filter_elem { $$ = $3; } + ; +reindex_filter_elem: + COLLATION { $$ = REINDEXOPT_COLLATION; } + ; /***************************************************************************** * diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 05ec7f3ac6..88cf88dff5 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -784,6 +784,9 @@ standard_ProcessUtility(PlannedStmt *pstmt, switch (stmt->kind) { case REINDEX_OBJECT_INDEX: + if ((stmt->options & REINDEXOPT_ALL_FILTERS) != 0) + elog(ERROR, "FILTER clause is not compatible with REINDEX INDEX"); + ReindexIndex(stmt->relation, stmt->options, stmt->concurrent); break; case REINDEX_OBJECT_TABLE: diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 2b992d7832..378592cf11 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -53,6 +53,7 @@ #include "catalog/pg_database.h" #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" +#include "catalog/pg_opfamily.h" #include "catalog/pg_partitioned_table.h" #include "catalog/pg_proc.h" #include "catalog/pg_publication.h" @@ -4433,6 +4434,96 @@ RelationGetIndexList(Relation relation) return result; } +/* + * RelationGetIndexListFiltered -- get a filtered list of indexes on this relation + * + * Calls RelationGetIndexList and filters out the result. + */ +List * +RelationGetIndexListFiltered(Relation relation, int options) +{ + List *result, + *full_list; + ListCell *lc; + + full_list = RelationGetIndexList(relation); + + /* Quick exit if no filtering was asked of if the list is empty. */ + if ((options & REINDEXOPT_ALL_FILTERS) == 0 || full_list == NIL) + return full_list; + + result = NIL; + foreach(lc, full_list) + { + Oid indexOid = lfirst_oid(lc); + Relation index; + int i, + numAtts; + bool keepit = false; + + index = index_open(indexOid, AccessShareLock); + numAtts = index->rd_index->indnatts; + index_close(index, AccessShareLock); + + for (i = 0; i < numAtts; i++) + { + /* + * If at least one column doesn't match the asked filter, we have + * to keep the whole index. + */ + if (keepit) + break; + + /* + * The caller wants to discard indexes that doesn't depend on a + * collation + */ + if (options & REINDEXOPT_COLLATION) + { + char typcategory; + bool typispreferred, + res; + Oid opclass, + opfamily = InvalidOid, + opcintype = InvalidOid; + + opclass = get_index_column_opclass(indexOid, i + 1); + + res = get_opclass_opfamily_and_input_type(opclass, &opfamily, + &opcintype); + + if (!res) + { + keepit = true; + break; + } + + /* + * text_pattern_ops and varchar_pattern_ops doesn't depend on + * a collation order. + */ + if (opfamily == TEXT_PATTERN_BTREE_FAM_OID || + opfamily == BPCHAR_PATTERN_BTREE_FAM_OID) + continue; + + get_type_category_preferred(opcintype, &typcategory, + &typispreferred); + + if (typcategory == TYPCATEGORY_STRING) + { + keepit = true; + break; + } + } + } + + if (keepit) + result = lappend_int(result, indexOid); + } + + return result; +} + /* * RelationGetStatExtList * get a list of OIDs of statistics objects on this relation diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 94ded3c135..a871bff556 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3301,6 +3301,10 @@ typedef struct ConstraintsSetStmt /* Reindex options */ #define REINDEXOPT_VERBOSE 1 << 0 /* print progress info */ +#define REINDEXOPT_COLLATION 1 << 1 /* only reindex collation-dependent + * indexes */ + +#define REINDEXOPT_ALL_FILTERS (REINDEXOPT_COLLATION) typedef enum ReindexObjectType { diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h index d9c10ffcba..c4ae25ef87 100644 --- a/src/include/utils/relcache.h +++ b/src/include/utils/relcache.h @@ -42,6 +42,7 @@ extern void RelationClose(Relation relation); /* * Routines to compute/retrieve additional cached information */ +extern List *RelationGetIndexListFiltered(Relation relation, int options); extern List *RelationGetFKeyList(Relation relation); extern List *RelationGetIndexList(Relation relation); extern List *RelationGetStatExtList(Relation relation); diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 9305649c11..4184c767db 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1936,6 +1936,17 @@ CREATE TABLE reindex_verbose(id integer primary key); \set VERBOSITY terse \\ -- suppress machine-dependent details REINDEX (VERBOSE) TABLE reindex_verbose; INFO: index "reindex_verbose_pkey" was reindexed +REINDEX (VERBOSE, FILTER = COLLATION) TABLE reindex_verbose; +NOTICE: table "reindex_verbose" has no indexes to reindex +-- One column, not depending on a collation +CREATE INDEX ON reindex_verbose ((id)::text text_pattern_ops); +ERROR: syntax error at or near "::" at character 38 +-- Two columns, none depending on a collation +CREATE INDEX ON reindex_verbose (id, (id::text) text_pattern_ops); +-- Three columns, one depending on a collation +CREATE INDEX ON reindex_verbose ((id::text), id, (id::text) text_pattern_ops); +REINDEX (VERBOSE, FILTER = COLLATION) TABLE reindex_verbose; +INFO: index "reindex_verbose_id_id1_id2_idx" was reindexed \set VERBOSITY default DROP TABLE reindex_verbose; -- diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index cd46f071bd..ee0336c9ca 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -744,6 +744,14 @@ explain (costs off) CREATE TABLE reindex_verbose(id integer primary key); \set VERBOSITY terse \\ -- suppress machine-dependent details REINDEX (VERBOSE) TABLE reindex_verbose; +REINDEX (VERBOSE, FILTER = COLLATION) TABLE reindex_verbose; +-- One column, not depending on a collation +CREATE INDEX ON reindex_verbose ((id)::text text_pattern_ops); +-- Two columns, none depending on a collation +CREATE INDEX ON reindex_verbose (id, (id::text) text_pattern_ops); +-- Three columns, one depending on a collation +CREATE INDEX ON reindex_verbose ((id::text), id, (id::text) text_pattern_ops); +REINDEX (VERBOSE, FILTER = COLLATION) TABLE reindex_verbose; \set VERBOSITY default DROP TABLE reindex_verbose;