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

Commit242066c

Browse files
committed
Speed up "brin" regression test a little bit.
In the large DO block, collect row TIDs into array variables instead ofcreating and dropping a pile of temporary tables. In a normal build,this reduces the brin test script's runtime from about 1.1 sec to 0.4 secon my workstation. That's not all that exciting perhaps, but in aCLOBBER_CACHE_ALWAYS test build, the runtime drops from 20 min to 17 min,which is a little more useful. In combination with some other changesI plan to propose, this will help provide a noticeable reduction incycle time for CLOBBER_CACHE_ALWAYS buildfarm critters.
1 parentac8eb97 commit242066c

File tree

2 files changed

+30
-54
lines changed

2 files changed

+30
-54
lines changed

‎src/test/regress/expected/brin.out

Lines changed: 15 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -288,13 +288,13 @@ DECLARE
288288
r record;
289289
r2 record;
290290
cond text;
291+
idx_ctids tid[];
292+
ss_ctids tid[];
291293
count int;
292-
mismatch bool;
293294
plan_ok bool;
294295
plan_line text;
295296
BEGIN
296297
FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP
297-
mismatch := false;
298298

299299
-- prepare the condition
300300
IF r.value IS NULL THEN
@@ -304,53 +304,46 @@ BEGIN
304304
END IF;
305305

306306
-- run the query using the brin index
307-
CREATE TEMP TABLE brin_result (cid tid);
308307
SET enable_seqscan = 0;
309308
SET enable_bitmapscan = 1;
310309

311310
plan_ok := false;
312-
FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest WHERE %s $y$, cond) LOOP
313-
IF plan_line LIKE 'Bitmap Heap Scan on brintest%' THEN
311+
FOR plan_line IN EXECUTE format($y$EXPLAIN SELECTarray_agg(ctid) FROM brintest WHERE %s $y$, cond) LOOP
312+
IF plan_line LIKE '%Bitmap Heap Scan on brintest%' THEN
314313
plan_ok := true;
315314
END IF;
316315
END LOOP;
317316
IF NOT plan_ok THEN
318317
RAISE WARNING 'did not get bitmap indexscan plan for %', r;
319318
END IF;
320319

321-
EXECUTE format($y$INSERT INTO brin_result SELECT ctid FROM brintest WHERE %s $y$, cond);
320+
EXECUTE format($y$SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond)
321+
INTO idx_ctids;
322322

323323
-- run the query using a seqscan
324-
CREATE TEMP TABLE brin_result_ss (cid tid);
325324
SET enable_seqscan = 1;
326325
SET enable_bitmapscan = 0;
327326

328327
plan_ok := false;
329-
FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT ctid FROM brintest WHERE %s $y$, cond) LOOP
330-
IF plan_line LIKE 'Seq Scan on brintest%' THEN
328+
FOR plan_line IN EXECUTE format($y$EXPLAIN SELECTarray_agg(ctid) FROM brintest WHERE %s $y$, cond) LOOP
329+
IF plan_line LIKE '%Seq Scan on brintest%' THEN
331330
plan_ok := true;
332331
END IF;
333332
END LOOP;
334333
IF NOT plan_ok THEN
335334
RAISE WARNING 'did not get seqscan plan for %', r;
336335
END IF;
337336

338-
EXECUTE format($y$INSERT INTO brin_result_ss SELECT ctid FROM brintest WHERE %s $y$, cond);
337+
EXECUTE format($y$SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond)
338+
INTO ss_ctids;
339339

340340
-- make sure both return the same results
341-
PERFORM * FROM brin_result EXCEPT ALL SELECT * FROM brin_result_ss;
342-
GET DIAGNOSTICS count = ROW_COUNT;
343-
IF count <> 0 THEN
344-
mismatch = true;
345-
END IF;
346-
PERFORM * FROM brin_result_ss EXCEPT ALL SELECT * FROM brin_result;
347-
GET DIAGNOSTICS count = ROW_COUNT;
348-
IF count <> 0 THEN
349-
mismatch = true;
350-
END IF;
341+
count := array_length(idx_ctids, 1);
351342

352-
-- report the results of each scan to make the differences obvious
353-
IF mismatch THEN
343+
IF NOT (count = array_length(ss_ctids, 1) AND
344+
idx_ctids @> ss_ctids AND
345+
idx_ctids <@ ss_ctids) THEN
346+
-- report the results of each scan to make the differences obvious
354347
RAISE WARNING 'something not right in %: count %', r, count;
355348
SET enable_seqscan = 1;
356349
SET enable_bitmapscan = 0;
@@ -366,12 +359,7 @@ BEGIN
366359
END IF;
367360

368361
-- make sure we found expected number of matches
369-
SELECT count(*) INTO count FROM brin_result;
370362
IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF;
371-
372-
-- drop the temporary tables
373-
DROP TABLE brin_result;
374-
DROP TABLE brin_result_ss;
375363
END LOOP;
376364
END;
377365
$x$;

‎src/test/regress/sql/brin.sql

Lines changed: 15 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -294,13 +294,13 @@ DECLARE
294294
r record;
295295
r2 record;
296296
condtext;
297+
idx_ctids tid[];
298+
ss_ctids tid[];
297299
countint;
298-
mismatch bool;
299300
plan_ok bool;
300301
plan_linetext;
301302
BEGIN
302303
FOR rINSELECT colname, oper, typ, value[ordinality], matches[ordinality]FROM brinopers, unnest(op) WITH ORDINALITYAS oper LOOP
303-
mismatch := false;
304304

305305
-- prepare the condition
306306
IFr.value ISNULL THEN
@@ -310,53 +310,46 @@ BEGIN
310310
END IF;
311311

312312
-- run the query using the brin index
313-
CREATE TEMP TABLE brin_result (cid tid);
314313
SET enable_seqscan=0;
315314
SET enable_bitmapscan=1;
316315

317316
plan_ok := false;
318-
FOR plan_lineIN EXECUTE format($y$EXPLAINSELECT ctidFROM brintestWHERE %s $y$, cond) LOOP
319-
IF plan_lineLIKE'Bitmap Heap Scan on brintest%' THEN
317+
FOR plan_lineIN EXECUTE format($y$EXPLAINSELECTarray_agg(ctid)FROM brintestWHERE %s $y$, cond) LOOP
318+
IF plan_lineLIKE'%Bitmap Heap Scan on brintest%' THEN
320319
plan_ok := true;
321320
END IF;
322321
END LOOP;
323322
IF NOT plan_ok THEN
324323
RAISE WARNING'did not get bitmap indexscan plan for %', r;
325324
END IF;
326325

327-
EXECUTE format($y$INSERT INTO brin_resultSELECT ctidFROM brintestWHERE %s $y$, cond);
326+
EXECUTE format($y$SELECT array_agg(ctid)FROM brintestWHERE %s $y$, cond)
327+
INTO idx_ctids;
328328

329329
-- run the query using a seqscan
330-
CREATE TEMP TABLE brin_result_ss (cid tid);
331330
SET enable_seqscan=1;
332331
SET enable_bitmapscan=0;
333332

334333
plan_ok := false;
335-
FOR plan_lineIN EXECUTE format($y$EXPLAINSELECT ctidFROM brintestWHERE %s $y$, cond) LOOP
336-
IF plan_lineLIKE'Seq Scan on brintest%' THEN
334+
FOR plan_lineIN EXECUTE format($y$EXPLAINSELECTarray_agg(ctid)FROM brintestWHERE %s $y$, cond) LOOP
335+
IF plan_lineLIKE'%Seq Scan on brintest%' THEN
337336
plan_ok := true;
338337
END IF;
339338
END LOOP;
340339
IF NOT plan_ok THEN
341340
RAISE WARNING'did not get seqscan plan for %', r;
342341
END IF;
343342

344-
EXECUTE format($y$INSERT INTO brin_result_ssSELECT ctidFROM brintestWHERE %s $y$, cond);
343+
EXECUTE format($y$SELECT array_agg(ctid)FROM brintestWHERE %s $y$, cond)
344+
INTO ss_ctids;
345345

346346
-- make sure both return the same results
347-
PERFORM*FROM brin_result EXCEPT ALLSELECT*FROM brin_result_ss;
348-
GET DIAGNOSTICS count= ROW_COUNT;
349-
IF count<>0 THEN
350-
mismatch= true;
351-
END IF;
352-
PERFORM*FROM brin_result_ss EXCEPT ALLSELECT*FROM brin_result;
353-
GET DIAGNOSTICS count= ROW_COUNT;
354-
IF count<>0 THEN
355-
mismatch= true;
356-
END IF;
347+
count := array_length(idx_ctids,1);
357348

358-
-- report the results of each scan to make the differences obvious
359-
IF mismatch THEN
349+
IF NOT (count= array_length(ss_ctids,1)AND
350+
idx_ctids @> ss_ctidsAND
351+
idx_ctids<@ ss_ctids) THEN
352+
-- report the results of each scan to make the differences obvious
360353
RAISE WARNING'something not right in %: count %', r, count;
361354
SET enable_seqscan=1;
362355
SET enable_bitmapscan=0;
@@ -372,12 +365,7 @@ BEGIN
372365
END IF;
373366

374367
-- make sure we found expected number of matches
375-
SELECTcount(*) INTO countFROM brin_result;
376368
IF count!=r.matches THEN RAISE WARNING'unexpected number of results % for %', count, r; END IF;
377-
378-
-- drop the temporary tables
379-
DROPTABLE brin_result;
380-
DROPTABLE brin_result_ss;
381369
END LOOP;
382370
END;
383371
$x$;

0 commit comments

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