This is an automated email from the ASF dual-hosted git repository. yjhjstz pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 10dd186c1a4156e4e87811d3b1816ea4e7995085 Author: Marbin Tan <[email protected]> AuthorDate: Wed Oct 4 10:13:54 2023 -0700 Fix long running execution for bitmap index When concurrently inserting to a table and then creating a bitmap index, there is a possibility that a select on that table will take a very long time to finish. The symptom shows 100% CPU utilization and the explain query may take an hour to complete. For more information see: https://github.com/greenplum-db/gpdb/issues/15389 Refactor the code such that it doesn't do anymore busy work. The previous implementation does a while loop of each BM_HRL_WORD_SIZE per fill. If the startTid and nextTid are extremely far apart with a large fillLength, then the while loop will take forever to finish. Since all we're doing in the while loop is advancing values, then this could be computed in a single pass. The change should cut down the explain query time from hours into milliseconds. Reviewed-by: Huansong Fu <[email protected]> --- src/backend/access/bitmap/bitmaputil.c | 44 +++++++++-------- .../expected/bitmap_index_concurrent.out | 56 ++++++++++++++++++++++ .../isolation2/sql/bitmap_index_concurrent.sql | 47 ++++++++++++++++++ 3 files changed, 127 insertions(+), 20 deletions(-) diff --git a/src/backend/access/bitmap/bitmaputil.c b/src/backend/access/bitmap/bitmaputil.c index 1210a4b060..d40341e192 100644 --- a/src/backend/access/bitmap/bitmaputil.c +++ b/src/backend/access/bitmap/bitmaputil.c @@ -400,32 +400,36 @@ _bitmap_catchup_to_next_tid(BMBatchWords *words, BMIterateResult *result) /* reset next tid to skip all empty words */ if (words->firstTid > result->nextTid) result->nextTid = words->firstTid; + continue; } - else + + if (fillLength > 0) { - while (fillLength > 0 && words->firstTid < result->nextTid) - { - /* update fill word to reflect expansion */ - words->cwords[result->lastScanWordNo]--; - words->firstTid += BM_HRL_WORD_SIZE; - fillLength--; - } + /* update fill word to reflect expansion */ - /* comsume all the fill words, try to fetch next words */ - if (fillLength == 0) - { - words->nwords--; - continue; - } + uint64 fillToUse = (result->nextTid - words->firstTid) / BM_HRL_WORD_SIZE + 1; + if (fillToUse > fillLength) + fillToUse = fillLength; - /* - * Catch up the next tid to search, but there still fill words. - * Return current state. - */ - if (words->firstTid >= result->nextTid) - return; + words->cwords[result->lastScanWordNo] -= fillToUse; + words->firstTid += fillToUse * BM_HRL_WORD_SIZE; + fillLength -= fillToUse; } + + /* comsume all the fill words, try to fetch next words */ + if (fillLength == 0) + { + words->nwords--; + continue; + } + + /* + * Catch up the next tid to search, but there still fill words. + * Return current state. + */ + if (words->firstTid >= result->nextTid) + return; } else { diff --git a/src/test/isolation2/expected/bitmap_index_concurrent.out b/src/test/isolation2/expected/bitmap_index_concurrent.out index 2a7d0e8a0e..b87cae7f47 100644 --- a/src/test/isolation2/expected/bitmap_index_concurrent.out +++ b/src/test/isolation2/expected/bitmap_index_concurrent.out @@ -508,3 +508,59 @@ SELECT count(*) FROM bmupdate WHERE id >= 97 and id <= 99 and gp_segment_id = 0; DROP TABLE bmupdate; DROP + +-- Regression test, when large amount of inserts concurrent inserts happen, +-- querying the table shouldn't take along time. +-- This test is from https://github.com/greenplum-db/gpdb/issues/15389 +-- Although planner is slower, this regression does not happen +-- for planner +set optimizer = on; +SET +DROP TABLE IF EXISTS bug.let_me_out; +DROP +DROP SCHEMA IF EXISTS bug; +DROP +CREATE SCHEMA bug; +CREATE +CREATE TABLE bug.let_me_out ( date_column date NULL, int_column int4 NULL ) WITH (appendonly = true, orientation = column) distributed randomly; +CREATE + +1&: INSERT INTO bug.let_me_out(date_column, int_column) SELECT ('2017-01-01'::timestamp + random() * ('2023-08-10'::timestamp - '2017-01-01'::timestamp))::date AS date_column, id / 50000 AS int_column -- id % 700 as int_column FROM generate_series(1, 30000000) s(id); <waiting ...> + +2&: INSERT INTO bug.let_me_out(date_column, int_column) SELECT ('2017-01-01'::timestamp + random() * ('2023-08-10'::timestamp - '2017-01-01'::timestamp))::date AS date_column, id / 50000 AS int_column -- id % 700 as int_column FROM generate_series(30000000, 50000000) s(id); <waiting ...> + +1<: <... completed> +INSERT 30000000 +2<: <... completed> +INSERT 20000001 + +CREATE INDEX idx_let_me_out__date_column ON bug.let_me_out USING bitmap (date_column); +CREATE +CREATE INDEX idx_let_me_out__int_column ON bug.let_me_out USING bitmap (int_column); +CREATE +VACUUM FULL ANALYZE bug.let_me_out; +VACUUM + +SET random_page_cost = 1; +SET +-- expected to finish under 250ms, but if we go over 60000, then something really bad happened +SET statement_timeout=60000; +SET +EXPLAIN ANALYZE SELECT date_column, int_column FROM bug.let_me_out WHERE date_column in ('2023-03-19', '2023-03-08', '2023-03-13', '2023-03-29', '2023-03-20', '2023-03-28', '2023-03-23', '2023-03-04', '2023-03-05', '2023-03-18', '2023-03-14', '2023-03-06', '2023-03-15', '2023-03-31', '2023-03-11', '2023-03-21', '2023-03-24', '2023-03-30', '2023-03-26', '2023-03-03', '2023-03-22', '2023-03-01', '2023-03-12', '2023-03-17', '2023-03-27', '2023-03-07', '2023-03-16', '2023-03-10', '2023-03-25 [...] + QUERY PLAN [...] +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ [...] + Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..448.82 rows=37406 width=8) (actual time=39.186..142.060 rows=20169 loops=1) [...] + -> Bitmap Heap Scan on let_me_out (cost=0.00..447.52 rows=12469 width=8) (actual time=36.948..131.712 rows=6817 loops=1) [...] + Recheck Cond: ((date_column = ANY ('{2023-03-19,2023-03-08,2023-03-13,2023-03-29,2023-03-20,2023-03-28,2023-03-23,2023-03-04,2023-03-05,2023-03-18,2023-03-14,2023-03-06,2023-03-15,2023-03-31,2023-03-11,2023-03-21,2023-03-24,2023-03-30,2023-03-26,2023-03-03,2023-03-22,2023-03-01,2023-03-12,2023-03-17,2023-03-27,2023-03-07,2023-03-16,2023-03-10,2023-03-25,2023-03-09,2023-03-02}'::date[])) AND (int_column = ANY ('{1003,1025,1026,1033,1034,1216,1221,160,161,1780,3049,305,3051,3052,3 [...] + -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) (actual time=23.072..23.073 rows=1 loops=1) [...] + -> Bitmap Index Scan on idx_let_me_out__date_column (cost=0.00..0.00 rows=0 width=0) (actual time=12.033..12.033 rows=31 loops=1) [...] + Index Cond: (date_column = ANY ('{2023-03-19,2023-03-08,2023-03-13,2023-03-29,2023-03-20,2023-03-28,2023-03-23,2023-03-04,2023-03-05,2023-03-18,2023-03-14,2023-03-06,2023-03-15,2023-03-31,2023-03-11,2023-03-21,2023-03-24,2023-03-30,2023-03-26,2023-03-03,2023-03-22,2023-03-01,2023-03-12,2023-03-17,2023-03-27,2023-03-07,2023-03-16,2023-03-10,2023-03-25,2023-03-09,2023-03-02}'::date[])) [...] + -> Bitmap Index Scan on idx_let_me_out__int_column (cost=0.00..0.00 rows=0 width=0) (actual time=11.035..11.035 rows=169 loops=1) [...] + Index Cond: (int_column = ANY ('{1003,1025,1026,1033,1034,1216,1221,160,161,1780,3049,305,3051,3052,3069,3077,3083,3084,3092,3121,3122,3123,3124,3180,3182,3183,3184,3193,3225,3226,3227,3228,3234,3267,3269,3270,3271,3272,3277,3301,3302,3303,3305,3307,3308,3310,3314,3317,3318,3319,3320,3321,3343,3344,3345,3347,3348,3388,339,341,345,346,347,349,3522,3565,3606,3607,3610,3612,3613,3637,3695,3738,3739,3740,3741,3742,3764,3829,3859,3861,3864,3865,3866,3867,3870,3871,3948,39 [...] + Optimizer: GPORCA [...] + Planning Time: 79.198 ms [...] + (slice0) Executor memory: 47K bytes. [...] + (slice1) Executor memory: 49133K bytes avg x 3 workers, 49133K bytes max (seg0). [...] + Memory used: 128000kB [...] + Execution Time: 148.774 ms [...] +(14 rows) diff --git a/src/test/isolation2/sql/bitmap_index_concurrent.sql b/src/test/isolation2/sql/bitmap_index_concurrent.sql index bbac9e91d6..d685a891ac 100644 --- a/src/test/isolation2/sql/bitmap_index_concurrent.sql +++ b/src/test/isolation2/sql/bitmap_index_concurrent.sql @@ -263,3 +263,50 @@ SELECT count(*) FROM bmupdate WHERE id >= 97 and id <= 99 and gp_segment_id = 0; DROP TABLE bmupdate; + +-- Regression test, when large amount of inserts concurrent inserts happen, +-- querying the table shouldn't take along time. +-- This test is from https://github.com/greenplum-db/gpdb/issues/15389 +-- Although planner is slower, this regression does not happen +-- for planner +set optimizer = on; +DROP TABLE IF EXISTS bug.let_me_out; +DROP SCHEMA IF EXISTS bug; +CREATE SCHEMA bug; +CREATE TABLE bug.let_me_out +( + date_column date NULL, + int_column int4 NULL +) +WITH (appendonly = true, orientation = column) +distributed randomly; + +1&: INSERT INTO bug.let_me_out(date_column, int_column) + SELECT ('2017-01-01'::timestamp + random() * ('2023-08-10'::timestamp - '2017-01-01'::timestamp))::date AS date_column, + id / 50000 AS int_column + -- id % 700 as int_column + FROM generate_series(1, 30000000) s(id); + +2&: INSERT INTO bug.let_me_out(date_column, int_column) + SELECT ('2017-01-01'::timestamp + random() * ('2023-08-10'::timestamp - '2017-01-01'::timestamp))::date AS date_column, + id / 50000 AS int_column + -- id % 700 as int_column + FROM generate_series(30000000, 50000000) s(id); + +1<: +2<: + +CREATE INDEX idx_let_me_out__date_column ON bug.let_me_out USING bitmap (date_column); +CREATE INDEX idx_let_me_out__int_column ON bug.let_me_out USING bitmap (int_column); +VACUUM FULL ANALYZE bug.let_me_out; + +SET random_page_cost = 1; +-- expected to finish under 250ms, but if we go over 60000, then something really bad happened +SET statement_timeout=60000; +EXPLAIN ANALYZE +SELECT date_column, + int_column +FROM bug.let_me_out +WHERE date_column in ('2023-03-19', '2023-03-08', '2023-03-13', '2023-03-29', '2023-03-20', '2023-03-28', '2023-03-23', '2023-03-04', '2023-03-05', '2023-03-18', '2023-03-14', '2023-03-06', '2023-03-15', '2023-03-31', '2023-03-11', '2023-03-21', '2023-03-24', '2023-03-30', '2023-03-26', '2023-03-03', '2023-03-22', '2023-03-01', '2023-03-12', '2023-03-17', '2023-03-27', '2023-03-07', '2023-03-16', '2023-03-10', '2023-03-25', '2023-03-09', '2023-03-02') +AND +int_column IN (1003,1025,1026,1033,1034,1216,1221,160,161,1780,3049,305,3051,3052,3069,3077,3083,3084,3092,3121,3122,3123,3124,3180,3182,3183,3184,3193,3225,3226,3227,3228,3234,3267,3269,3270,3271,3272,3277,3301,3302,3303,3305,3307,3308,3310,3314,3317,3318,3319,3320,3321,3343,3344,3345,3347,3348,3388,339,341,345,346,347,349,3522,3565,3606,3607,3610,3612,3613,3637,3695,3738,3739,3740,3741,3742,3764,3829,3859,3861,3864,3865,3866,3867,3870,3871,3948,3967,3969,3971,3974,3975,3976,4043,4059,4 [...] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
