8000 Test suite for skip scan project · petergeoghegan/postgres@679f158 · GitHub
[go: up one dir, main page]
Skip to content

Commit 679f158

Browse files
petergeogheganpetergeoghegan
committed
Test suite for skip scan project
Also includes tests that originate from old test suite for dynamic SAOP project, April 2024. These tests are essentially a fork of the originals, that freely use skip scan stuff in ways not originally anticipated by the tests.
1 parent e2340ba commit 679f158

File tree

75 files changed

+937141
-0
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

75 files changed

+937141
-0
lines changed

‎.gitignore

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,3 +43,6 @@ lib*.pc
4343
/Release/
4444
/tmp_install/
4545
/portlock/
46+
debug.log
47+
results.csv
48+
test.log

‎coleman_regress_test.sql

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
set work_mem='100MB';
2+
set effective_io_concurrency=100;
3+
set effective_cache_size='24GB';
4+
set maintenance_io_concurrency=100;
5+
set random_page_cost=2.0;
6+
set track_io_timing to off;
7+
set enable_seqscan to off;
8+
set client_min_messages=error;
9+
set vacuum_freeze_min_age = 0;
10+
create extension if not exists pageinspect; -- just to have it
11+
reset client_min_messages;
12+
13+
-- Set log_btree_verbosity to 1 without depending on having that patch
14+
-- applied (HACK, just sets commit_siblings instead when we don't have that
15+
-- patch available):
16+
select set_config((select coalesce((select name from pg_settings where name = 'log_btree_verbosity'), 'commit_siblings')), '1', false);
17+
18+
---------------------------------------
19+
-- James Coleman's test case variant --
20+
---------------------------------------
21+
22+
-- Per https://www.postgresql.org/message-id/flat/CAAaqYe-SsHgXKXPpjn7WCTUnB_RQSxXjpSaJd32aA%3DRquv0AgQ%40mail.gmail.com,
23+
-- though I'm going to use my own index definition for this
24+
25+
set client_min_messages=error;
26+
drop table if exists coleman_regress;
27+
reset client_min_messages;
28+
select setseed(0.12345); -- Need deterministic test case
29+
create unlogged table coleman_regress(
30+
bar_fk integer,
31+
created_at timestamptz
32+
);
33+
34+
create index index_coleman_regress on coleman_regress(created_at, bar_fk);
35+
36+
insert into coleman_regress(bar_fk, created_at)
37+
select i % 1000, '2000-01-01'::timestamptz -(random() * '5 years'::interval)
38+
from generate_series(1, 500000) t(i);
39+
40+
VACUUM (freeze,analyze) coleman_regress;
41+
42+
-- This cannot really skip, but must not pay too high a cost in CPU cycles for
43+
-- being open to the possibility of skipping:
44+
set skipscan_prefix_cols = 32;
45+
select *
46+
from coleman_regress
47+
where bar_fk = 1
48+
order by created_at
49+
limit 15;
50+
-- 76 buffer hits total for parity with master:
51+
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF, COSTS OFF) -- need "costs off" here
52+
select *
53+
from coleman_regress
54+
where bar_fk = 1
55+
order by created_at
56+
limit 15;
57+
58+
-- Same again, with skipping disabled using GUC (representative of master):
59+
set skipscan_prefix_cols = 0;
60+
select *
61+
from coleman_regress
62+
where bar_fk = 1
63+
order by created_at
64+
limit 15;
65+
-- 76 buffer hits total for parity with master:
66+
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF, COSTS OFF) -- need "costs off" here
67+
select *
68+
from coleman_regress
69+
where bar_fk = 1
70+
order by created_at
71+
limit 15;

‎microbenchmarks/.gitignore

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
skipscan_costs.csv

0 commit comments

Comments
 (0)
0