From 40d964ec997f64227bc0ff5e058dc4a5770a70a9 Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Mon, 20 Jan 2020 07:57:49 +0530 Subject: Allow vacuum command to process indexes in parallel. This feature allows the vacuum to leverage multiple CPUs in order to process indexes. This enables us to perform index vacuuming and index cleanup with background workers. This adds a PARALLEL option to VACUUM command where the user can specify the number of workers that can be used to perform the command which is limited by the number of indexes on a table. Specifying zero as a number of workers will disable parallelism. This option can't be used with the FULL option. Each index is processed by at most one vacuum process. Therefore parallel vacuum can be used when the table has at least two indexes. The parallel degree is either specified by the user or determined based on the number of indexes that the table has, and further limited by max_parallel_maintenance_workers. The index can participate in parallel vacuum iff it's size is greater than min_parallel_index_scan_size. Author: Masahiko Sawada and Amit Kapila Reviewed-by: Dilip Kumar, Amit Kapila, Robert Haas, Tomas Vondra, Mahendra Singh and Sergei Kornilov Tested-by: Mahendra Singh and Prabhat Sahu Discussion: https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com https://postgr.es/m/CAA4eK1J-VoR9gzS5E75pcD-OH0mEyCdp8RihcwKrcuw7J-Q0+w@mail.gmail.com --- src/test/regress/expected/vacuum.out | 34 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/vacuum.sql | 31 +++++++++++++++++++++++++++++++ 2 files changed, 65 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 9996d882d16..f4250a433ae 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -92,6 +92,40 @@ CONTEXT: SQL function "do_analyze" statement 1 SQL function "wrap_do_analyze" statement 1 VACUUM FULL vactst; VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; +-- PARALLEL option +CREATE TABLE pvactst (i INT, a INT[], p POINT) with (autovacuum_enabled = off); +INSERT INTO pvactst SELECT i, array[1,2,3], point(i, i+1) FROM generate_series(1,1000) i; +CREATE INDEX btree_pvactst ON pvactst USING btree (i); +CREATE INDEX hash_pvactst ON pvactst USING hash (i); +CREATE INDEX brin_pvactst ON pvactst USING brin (i); +CREATE INDEX gin_pvactst ON pvactst USING gin (a); +CREATE INDEX gist_pvactst ON pvactst USING gist (p); +CREATE INDEX spgist_pvactst ON pvactst USING spgist (p); +-- VACUUM invokes parallel index cleanup +SET min_parallel_index_scan_size to 0; +VACUUM (PARALLEL 2) pvactst; +-- VACUUM invokes parallel bulk-deletion +UPDATE pvactst SET i = i WHERE i < 1000; +VACUUM (PARALLEL 2) pvactst; +UPDATE pvactst SET i = i WHERE i < 1000; +VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum +VACUUM (PARALLEL -1) pvactst; -- error +ERROR: parallel vacuum degree must be between 0 and 1024 +LINE 1: VACUUM (PARALLEL -1) pvactst; + ^ +VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst; +VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL +ERROR: cannot specify both FULL and PARALLEL options +VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree +ERROR: parallel option requires a value between 0 and 1024 +LINE 1: VACUUM (PARALLEL) pvactst; + ^ +CREATE TEMPORARY TABLE tmp (a int PRIMARY KEY); +CREATE INDEX tmp_idx1 ON tmp (a); +VACUUM (PARALLEL 1) tmp; -- disables parallel vacuum option +WARNING: disabling parallel option of vacuum on "tmp" --- cannot vacuum temporary tables in parallel +RESET min_parallel_index_scan_size; +DROP TABLE pvactst; -- INDEX_CLEANUP option CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT); -- Use uncompressed data stored in toast. diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 69987f75e9b..cf741f7b114 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -75,6 +75,37 @@ VACUUM FULL vactst; VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; +-- PARALLEL option +CREATE TABLE pvactst (i INT, a INT[], p POINT) with (autovacuum_enabled = off); +INSERT INTO pvactst SELECT i, array[1,2,3], point(i, i+1) FROM generate_series(1,1000) i; +CREATE INDEX btree_pvactst ON pvactst USING btree (i); +CREATE INDEX hash_pvactst ON pvactst USING hash (i); +CREATE INDEX brin_pvactst ON pvactst USING brin (i); +CREATE INDEX gin_pvactst ON pvactst USING gin (a); +CREATE INDEX gist_pvactst ON pvactst USING gist (p); +CREATE INDEX spgist_pvactst ON pvactst USING spgist (p); + +-- VACUUM invokes parallel index cleanup +SET min_parallel_index_scan_size to 0; +VACUUM (PARALLEL 2) pvactst; + +-- VACUUM invokes parallel bulk-deletion +UPDATE pvactst SET i = i WHERE i < 1000; +VACUUM (PARALLEL 2) pvactst; + +UPDATE pvactst SET i = i WHERE i < 1000; +VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum + +VACUUM (PARALLEL -1) pvactst; -- error +VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst; +VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL +VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree +CREATE TEMPORARY TABLE tmp (a int PRIMARY KEY); +CREATE INDEX tmp_idx1 ON tmp (a); +VACUUM (PARALLEL 1) tmp; -- disables parallel vacuum option +RESET min_parallel_index_scan_size; +DROP TABLE pvactst; + -- INDEX_CLEANUP option CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT); -- Use uncompressed data stored in toast. -- cgit v1.2.3