Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit47f99a4

Browse files
committed
reindexdb: Add the index-level REINDEX with multiple jobs
Straight-forward index-level REINDEX is not supported with multiple jobs aswe cannot control the concurrent processing of multiple indexes depending onthe same relation. Instead, we dedicate the whole table to certain reindexjob. Thus, if indexes in the lists belong to different tables, that gives usa fair level of parallelism.This commit teaches get_parallel_object_list() to fetch table names forindexes in the case of index-level REINDEX. The same tables are groupedtogether in the output order, and the list of indexes is also rebuilt tomatch that order. Later during processingof that list, we push indexesbelonging to the same table into the same job.Discussion:https://postgr.es/m/CACG%3DezZU_VwDi-1PN8RUSE6mcYG%2BYx1NH_rJO4%2BKe-mKqLp%3DNw%40mail.gmail.comAuthor: Maxim Orlov, Svetlana Derevyanko, Alexander KorotkovReviewed-by: Michael Paquier
1 parent503c0ad commit47f99a4

File tree

3 files changed

+130
-27
lines changed

3 files changed

+130
-27
lines changed

‎doc/src/sgml/ref/reindexdb.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -179,8 +179,7 @@ PostgreSQL documentation
179179
setting is high enough to accommodate all connections.
180180
</para>
181181
<para>
182-
Note that this option is incompatible with the <option>--index</option>
183-
and <option>--system</option> options.
182+
Note that this option is incompatible with the <option>--system</option> option.
184183
</para>
185184
</listitem>
186185
</varlistentry>

‎src/bin/scripts/reindexdb.c

Lines changed: 124 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -206,19 +206,8 @@ main(int argc, char *argv[])
206206

207207
setup_cancel_handler(NULL);
208208

209-
if (concurrentCons>1)
210-
{
211-
/*
212-
* Index-level REINDEX is not supported with multiple jobs as we
213-
* cannot control the concurrent processing of multiple indexes
214-
* depending on the same relation.
215-
*/
216-
if (indexes.head!=NULL)
217-
pg_fatal("cannot use multiple jobs to reindex indexes");
218-
219-
if (syscatalog)
220-
pg_fatal("cannot use multiple jobs to reindex system catalogs");
221-
}
209+
if (concurrentCons>1&&syscatalog)
210+
pg_fatal("cannot use multiple jobs to reindex system catalogs");
222211

223212
if (alldb)
224213
{
@@ -258,7 +247,7 @@ main(int argc, char *argv[])
258247
if (indexes.head!=NULL)
259248
reindex_one_database(&cparams,REINDEX_INDEX,&indexes,
260249
progname,echo,verbose,
261-
concurrently,1,tablespace);
250+
concurrently,concurrentCons,tablespace);
262251

263252
if (tables.head!=NULL)
264253
reindex_one_database(&cparams,REINDEX_TABLE,&tables,
@@ -288,12 +277,16 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
288277
{
289278
PGconn*conn;
290279
SimpleStringListCell*cell;
280+
SimpleStringListCell*indices_tables_cell;
291281
boolparallel=concurrentCons>1;
292282
SimpleStringList*process_list=user_list;
283+
SimpleStringList*indices_tables_list=NULL;
293284
ReindexTypeprocess_type=type;
294285
ParallelSlotArray*sa;
295286
boolfailed= false;
296287
intitems_count=0;
288+
char*prev_index_table_name=NULL;
289+
ParallelSlot*free_slot=NULL;
297290

298291
conn=connectDatabase(cparams,progname,echo, false, true);
299292

@@ -363,8 +356,25 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
363356
return;
364357
break;
365358

366-
caseREINDEX_SYSTEM:
367359
caseREINDEX_INDEX:
360+
Assert(user_list!=NULL);
361+
362+
/*
363+
* Build a list of relations from the indices. This will
364+
* accordingly reorder the list of indices too.
365+
*/
366+
indices_tables_list=get_parallel_object_list(conn,process_type,
367+
user_list,echo);
368+
369+
if (indices_tables_list)
370+
indices_tables_cell=indices_tables_list->head;
371+
372+
/* Bail out if nothing to process */
373+
if (process_list==NULL)
374+
return;
375+
break;
376+
377+
caseREINDEX_SYSTEM:
368378
/* not supported */
369379
Assert(false);
370380
break;
@@ -404,22 +414,41 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
404414
do
405415
{
406416
constchar*objname=cell->val;
407-
ParallelSlot*free_slot=NULL;
417+
boolneed_new_slot= true;
408418

409419
if (CancelRequested)
410420
{
411421
failed= true;
412422
gotofinish;
413423
}
414424

415-
free_slot=ParallelSlotsGetIdle(sa,NULL);
416-
if (!free_slot)
425+
/*
426+
* For parallel index-level REINDEX, the indices of the same table are
427+
* ordered together and they are to be processed by the same job. So,
428+
* we don't switch the job as soon as the index belongs to the same
429+
* table as the previous one.
430+
*/
431+
if (parallel&&process_type==REINDEX_INDEX)
432+
{
433+
if (prev_index_table_name!=NULL&&
434+
strcmp(prev_index_table_name,indices_tables_cell->val)==0)
435+
need_new_slot= false;
436+
prev_index_table_name=indices_tables_cell->val;
437+
indices_tables_cell=indices_tables_cell->next;
438+
}
439+
440+
if (need_new_slot)
417441
{
418-
failed= true;
419-
gotofinish;
442+
free_slot=ParallelSlotsGetIdle(sa,NULL);
443+
if (!free_slot)
444+
{
445+
failed= true;
446+
gotofinish;
447+
}
448+
449+
ParallelSlotSetHandler(free_slot,TableCommandResultHandler,NULL);
420450
}
421451

422-
ParallelSlotSetHandler(free_slot,TableCommandResultHandler,NULL);
423452
run_reindex_command(free_slot->connection,process_type,objname,
424453
echo,verbose,concurrently, true,tablespace);
425454

@@ -436,6 +465,12 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
436465
pg_free(process_list);
437466
}
438467

468+
if (indices_tables_list)
469+
{
470+
simple_string_list_destroy(indices_tables_list);
471+
pg_free(indices_tables_list);
472+
}
473+
439474
ParallelSlotsTerminate(sa);
440475
pfree(sa);
441476

@@ -647,8 +682,61 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
647682
}
648683
break;
649684

650-
caseREINDEX_SYSTEM:
651685
caseREINDEX_INDEX:
686+
{
687+
SimpleStringListCell*cell;
688+
689+
Assert(user_list!=NULL);
690+
691+
/*
692+
* Straight-forward index-level REINDEX is not supported with
693+
* multiple jobs as we cannot control the concurrent
694+
* processing of multiple indexes depending on the same
695+
* relation. But we can extract the appropriate table name
696+
* for the index and put REINDEX INDEX commands into different
697+
* jobs, according to the parent tables.
698+
*
699+
* We will order the results to group the same tables
700+
* together. We fetch index names as well to build a new list
701+
* of them with matching order.
702+
*/
703+
appendPQExpBufferStr(&catalog_query,
704+
"SELECT t.relname, n.nspname, i.relname\n"
705+
"FROM pg_catalog.pg_index x\n"
706+
"JOIN pg_catalog.pg_class t ON t.oid = x.indrelid\n"
707+
"JOIN pg_catalog.pg_class i ON i.oid = x.indexrelid\n"
708+
"LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.relnamespace\n"
709+
"WHERE x.indexrelid OPERATOR(pg_catalog.=) ANY(ARRAY['");
710+
711+
for (cell=user_list->head;cell;cell=cell->next)
712+
{
713+
if (cell!=user_list->head)
714+
appendPQExpBufferStr(&catalog_query,"', '");
715+
716+
appendQualifiedRelation(&catalog_query,cell->val,conn,echo);
717+
}
718+
719+
/*
720+
* Order tables by the size of its greatest index. Within the
721+
* table, order indexes by their sizes.
722+
*/
723+
appendPQExpBufferStr(&catalog_query,
724+
"']::pg_catalog.regclass[])\n"
725+
"ORDER BY max(i.relpages) OVER \n"
726+
" (PARTITION BY n.nspname, t.relname),\n"
727+
" n.nspname, t.relname, i.relpages;\n");
728+
729+
/*
730+
* We're going to re-order the user_list to match the order of
731+
* tables. So, empty the user_list to fill it from the query
732+
* result.
733+
*/
734+
simple_string_list_destroy(user_list);
735+
user_list->head=user_list->tail=NULL;
736+
}
737+
break;
738+
739+
caseREINDEX_SYSTEM:
652740
caseREINDEX_TABLE:
653741
Assert(false);
654742
break;
@@ -680,6 +768,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
680768

681769
simple_string_list_append(tables,buf.data);
682770
resetPQExpBuffer(&buf);
771+
772+
if (type==REINDEX_INDEX)
773+
{
774+
/*
775+
* For index-level REINDEX, rebuild the list of indexes to match
776+
* the order of tables list.
777+
*/
778+
appendPQExpBufferStr(&buf,
779+
fmtQualifiedId(PQgetvalue(res,i,1),
780+
PQgetvalue(res,i,2)));
781+
782+
simple_string_list_append(user_list,buf.data);
783+
resetPQExpBuffer(&buf);
784+
}
683785
}
684786
termPQExpBuffer(&buf);
685787
PQclear(res);

‎src/bin/scripts/t/090_reindexdb.pl

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -236,19 +236,21 @@
236236
CREATE SCHEMA s1;
237237
CREATE TABLE s1.t1(id integer);
238238
CREATE INDEX ON s1.t1(id);
239+
CREATE INDEX i1 ON s1.t1(id);
239240
CREATE SCHEMA s2;
240241
CREATE TABLE s2.t2(id integer);
241242
CREATE INDEX ON s2.t2(id);
243+
CREATE INDEX i2 ON s2.t2(id);
242244
-- empty schema
243245
CREATE SCHEMA s3;
244246
|);
245247

246248
$node->command_fails(
247249
['reindexdb','-j','2','-s','postgres' ],
248250
'parallel reindexdb cannot process system catalogs');
249-
$node->command_fails(
250-
['reindexdb','-j','2','-i','i1','postgres' ],
251-
'parallel reindexdbcannot process indexes');
251+
$node->command_ok(
252+
['reindexdb','-j','2','-i','s1.i1','-i','s2.i2','postgres' ],
253+
'parallel reindexdbfor indices');
252254
# Note that the ordering of the commands is not stable, so the second
253255
# command for s2.t2 is not checked after.
254256
$node->issues_sql_like(

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp