compare_cost_with_v2_patch.out
application/octet-stream
Filename: compare_cost_with_v2_patch.out
Type: application/octet-stream
Part: 1
-- prepare
DROP TABLE IF EXISTS test;
DROP TABLE
CREATE TABLE test (id1 int2, id2 int4, id3 int8, value varchar(32));
CREATE TABLE
INSERT INTO test (SELECT i%11, i%103, i%1009, 'hello' FROM generate_series(1,1000000) s(i));
INSERT 0 1000000
-- CREATE INDEX idx_id3 ON test(id3);
-- CREATE INDEX idx_id1_id3 ON test(id1, id3);
-- CREATE INDEX idx_id2_id3 ON test(id2, id3);
-- CREATE INDEX idx_id1_id2_id3 ON test(id1, id2, id3);
ANALYZE;
ANALYZE
-- prepare
SET skipscan_prefix_cols = 3;
SET
SET enable_seqscan = off;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
DROP EXTENSION IF EXISTS pg_prewarm;
DROP EXTENSION
CREATE EXTENSION pg_prewarm;
CREATE EXTENSION
SELECT pg_prewarm('test');
pg_prewarm
------------
6370
(1 row)
-- seqscan
SET enable_seqscan = on;
SET
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..12676.73 rows=984 width=20) (actual time=0.856..113.861 rows=991 loops=1)
Output: id1, id2, id3, value
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=6370
-> Parallel Seq Scan on public.test (cost=0.00..11578.33 rows=410 width=20) (actual time=0.061..102.016 rows=330 loops=3)
Output: id1, id2, id3, value
Filter: (test.id3 = 101)
Rows Removed by Filter: 333003
Buffers: shared hit=6370
Worker 0: actual time=0.099..98.014 rows=315 loops=1
Buffers: shared hit=2066
Worker 1: actual time=0.054..97.162 rows=299 loops=1
Buffers: shared hit=1858
Planning:
Buffers: shared hit=19
Planning Time: 0.194 ms
Execution Time: 114.129 ms
(18 rows)
SET enable_seqscan = off;
SET
-- indexscan
SET enable_indexscan = on;
SET
CREATE INDEX idx_id3 ON test(id3);
CREATE INDEX
SELECT pg_prewarm('idx_id3');
pg_prewarm
------------
904
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id3 on public.test (cost=0.42..3677.64 rows=984 width=20) (actual time=0.048..1.576 rows=991 loops=1)
Output: id1, id2, id3, value
Index Cond: (test.id3 = 101)
Buffers: shared hit=998
Planning:
Buffers: shared hit=5
Planning Time: 0.140 ms
Execution Time: 1.706 ms
(8 rows)
DROP INDEX idx_id3;
DROP INDEX
CREATE INDEX idx_id1_id3 ON test(id1, id3);
CREATE INDEX
SELECT pg_prewarm('idx_id1_id3');
pg_prewarm
------------
861
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id1_id3 on public.test (cost=0.42..3706.37 rows=984 width=20) (actual time=0.125..1.974 rows=991 loops=1)
Output: id1, id2, id3, value
Index Cond: (test.id3 = 101)
Buffers: shared hit=1033
Planning:
Buffers: shared hit=6
Planning Time: 0.147 ms
Execution Time: 2.089 ms
(8 rows)
DROP INDEX idx_id1_id3;
DROP INDEX
CREATE INDEX idx_id2_id3 ON test(id2, id3);
CREATE INDEX
SELECT pg_prewarm('idx_id2_id3');
pg_prewarm
------------
1263
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id2_id3 on public.test (cost=0.42..3721.39 rows=984 width=20) (actual time=0.196..3.916 rows=991 loops=1)
Output: id1, id2, id3, value
Index Cond: (test.id3 = 101)
Buffers: shared hit=1309
Planning:
Buffers: shared hit=6
Planning Time: 0.906 ms
Execution Time: 4.108 ms
(8 rows)
DROP INDEX idx_id2_id3;
DROP INDEX
CREATE INDEX idx_id1_id2_id3 ON test(id1, id2, id3);
CREATE INDEX
SELECT pg_prewarm('idx_id1_id2_id3');
pg_prewarm
------------
3853
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id1_id2_id3 on public.test (cost=0.42..26562.77 rows=984 width=20) (actual time=0.051..15.533 rows=991 loops=1)
Output: id1, id2, id3, value
Index Cond: (test.id3 = 101)
Buffers: shared hit=4402
Planning:
Buffers: shared hit=7
Planning Time: 0.234 ms
Execution Time: 15.711 ms
(8 rows)
DROP INDEX idx_id1_id2_id3;
DROP INDEX
SET enable_indexscan = off;
SET
-- bitmapscan
SET enable_bitmapscan = on;
SET
CREATE INDEX idx_id3 ON test(id3);
CREATE INDEX
SELECT pg_prewarm('idx_id3');
pg_prewarm
------------
904
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=12.05..2641.70 rows=984 width=20) (actual time=0.626..2.856 rows=991 loops=1)
Output: id1, id2, id3, value
Recheck Cond: (test.id3 = 101)
Heap Blocks: exact=991
Buffers: shared hit=995
-> Bitmap Index Scan on idx_id3 (cost=0.00..11.80 rows=984 width=0) (actual time=0.317..0.317 rows=991 loops=1)
Index Cond: (test.id3 = 101)
Buffers: shared hit=4
Planning:
Buffers: shared hit=7
Planning Time: 0.154 ms
Execution Time: 2.992 ms
(12 rows)
DROP INDEX idx_id3;
DROP INDEX
CREATE INDEX idx_id1_id3 ON test(id1, id3);
CREATE INDEX
SELECT pg_prewarm('idx_id1_id3');
pg_prewarm
------------
861
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=56.26..2685.91 rows=984 width=20) (actual time=0.811..2.847 rows=991 loops=1)
Output: id1, id2, id3, value
Recheck Cond: (test.id3 = 101)
Heap Blocks: exact=991
Buffers: shared hit=1030
-> Bitmap Index Scan on idx_id1_id3 (cost=0.00..56.02 rows=984 width=0) (actual time=0.456..0.457 rows=991 loops=1)
Index Cond: (test.id3 = 101)
Buffers: shared hit=39
Planning:
Buffers: shared hit=6
Planning Time: 0.153 ms
Execution Time: 2.994 ms
(12 rows)
DROP INDEX idx_id1_id3;
DROP INDEX
CREATE INDEX idx_id2_id3 ON test(id2, id3);
CREATE INDEX
SELECT pg_prewarm('idx_id2_id3');
pg_prewarm
------------
1263
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=56.26..2685.91 rows=984 width=20) (actual time=1.628..4.139 rows=991 loops=1)
Output: id1, id2, id3, value
Recheck Cond: (test.id3 = 101)
Heap Blocks: exact=991
Buffers: shared hit=1306
-> Bitmap Index Scan on idx_id2_id3 (cost=0.00..56.02 rows=984 width=0) (actual time=1.291..1.292 rows=991 loops=1)
Index Cond: (test.id3 = 101)
Buffers: shared hit=315
Planning:
Buffers: shared hit=6
Planning Time: 0.169 ms
Execution Time: 4.280 ms
(12 rows)
DROP INDEX idx_id2_id3;
DROP INDEX
CREATE INDEX idx_id1_id2_id3 ON test(id1, id2, id3);
CREATE INDEX
SELECT pg_prewarm('idx_id1_id2_id3');
pg_prewarm
------------
3853
(1 row)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM test WHERE id3 = 101;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=22912.67..25542.32 rows=984 width=20) (actual time=11.753..13.879 rows=991 loops=1)
Output: id1, id2, id3, value
Recheck Cond: (test.id3 = 101)
Heap Blocks: exact=991
Buffers: shared hit=4402
-> Bitmap Index Scan on idx_id1_id2_id3 (cost=0.00..22912.42 rows=984 width=0) (actual time=11.405..11.406 rows=991 loops=1)
Index Cond: (test.id3 = 101)
Buffers: shared hit=3411
Planning:
Buffers: shared hit=6
Planning Time: 0.182 ms
Execution Time: 14.018 ms
(12 rows)
DROP INDEX idx_id1_id2_id3;
DROP INDEX
SET enable_bitmapscan = off;
SET