1--
2-- Testing memory usage of columnar tables.
3--
4
5CREATE SCHEMA columnar_memory;
6SET search_path TO 'columnar_memory';
7
8SET columnar.stripe_row_limit TO 50000;
9SET columnar.compression TO 'pglz';
10CREATE TABLE t (a int, tag text, memusage bigint) USING columnar;
11
12-- measure memory before doing writes
13SELECT TopMemoryContext as top_pre,
14	   WriteStateContext write_pre
15FROM columnar_test_helpers.columnar_store_memory_stats() \gset
16
17BEGIN;
18SET LOCAL client_min_messages TO DEBUG1;
19
20-- measure memory just before flushing 1st stripe
21INSERT INTO t
22 SELECT i, 'first batch',
23        -- sample memusage instead of recording everyr row for speed
24        CASE WHEN i % 100 = 0 THEN columnar_test_helpers.top_memory_context_usage() ELSE 0 END
25 FROM generate_series(1, 49999) i;
26SELECT TopMemoryContext as top0,
27       TopTransactionContext xact0,
28	   WriteStateContext write0
29FROM columnar_test_helpers.columnar_store_memory_stats() \gset
30
31-- flush 1st stripe, and measure memory just before flushing 2nd stripe
32INSERT INTO t
33 SELECT i, 'second batch', 0 /* no need to record memusage per row */
34 FROM generate_series(1, 50000) i;
35SELECT TopMemoryContext as top1,
36       TopTransactionContext xact1,
37	   WriteStateContext write1
38FROM columnar_test_helpers.columnar_store_memory_stats() \gset
39
40-- flush 2nd stripe, and measure memory just before flushing 3rd stripe
41INSERT INTO t
42 SELECT i, 'third batch', 0 /* no need to record memusage per row */
43 FROM generate_series(1, 50000) i;
44SELECT TopMemoryContext as top2,
45       TopTransactionContext xact2,
46	   WriteStateContext write2
47FROM columnar_test_helpers.columnar_store_memory_stats() \gset
48
49-- insert a large batch
50INSERT INTO t
51 SELECT i, 'large batch',
52        -- sample memusage instead of recording everyr row for speed
53        CASE WHEN i % 100 = 0 THEN columnar_test_helpers.top_memory_context_usage() ELSE 0 END
54 FROM generate_series(1, 100000) i;
55
56COMMIT;
57
58-- measure memory after doing writes
59SELECT TopMemoryContext as top_post,
60	   WriteStateContext write_post
61FROM columnar_test_helpers.columnar_store_memory_stats() \gset
62
63\x
64SELECT (1.0 * :top2/:top1 BETWEEN 0.99 AND 1.01) AS top_growth_ok,
65	   (1.0 * :xact1/:xact0 BETWEEN 0.99 AND 1.01) AND
66	   (1.0 * :xact2/:xact0 BETWEEN 0.99 AND 1.01) AS xact_growth_ok,
67	   (1.0 * :write1/:write0 BETWEEN 0.99 AND 1.01) AND
68	   (1.0 * :write2/:write0 BETWEEN 0.99 AND 1.01) AS write_growth_ok,
69	   :write_pre = 0 AND :write_post = 0 AS write_clear_outside_xact;
70
71-- inserting another bunch of rows should not grow top memory context
72INSERT INTO t
73 SELECT i, 'last batch', 0 /* no need to record memusage per row */
74 FROM generate_series(1, 50000) i;
75
76SELECT 1.0 * TopMemoryContext / :top_post BETWEEN 0.98 AND 1.02 AS top_growth_ok
77FROM columnar_test_helpers.columnar_store_memory_stats();
78
79-- before this change, max mem usage while executing inserts was 28MB and
80-- with this change it's less than 8MB.
81SELECT
82 (SELECT max(memusage) < 8 * 1024 * 1024 FROM t WHERE tag='large batch') AS large_batch_ok,
83 (SELECT max(memusage) < 8 * 1024 * 1024 FROM t WHERE tag='first batch') AS first_batch_ok;
84
85\x
86
87SELECT count(*) FROM t;
88
89SET client_min_messages TO WARNING;
90DROP SCHEMA columnar_memory CASCADE;
91