-- -- Test block filtering in cstore_fdw using min/max values in stripe skip lists. -- -- -- filtered_row_count returns number of rows filtered by the WHERE clause. -- If blocks get filtered by cstore_fdw, less rows are passed to WHERE -- clause, so this function should return a lower number. -- CREATE OR REPLACE FUNCTION filtered_row_count (query text) RETURNS bigint AS $$ DECLARE result bigint; rec text; BEGIN result := 0; FOR rec IN EXECUTE 'EXPLAIN ANALYZE ' || query LOOP IF rec ~ '^\s+Rows Removed by Filter' then result := regexp_replace(rec, '[^0-9]*', '', 'g'); END IF; END LOOP; RETURN result; END; $$ LANGUAGE PLPGSQL; -- Create and load data CREATE FOREIGN TABLE test_block_filtering (a int) SERVER cstore_server OPTIONS(filename '@abs_srcdir@/data/block_filtering.cstore', block_row_count '1000', stripe_row_count '2000'); COPY test_block_filtering FROM '@abs_srcdir@/data/block_filtering.csv' WITH CSV; -- Verify that filtered_row_count is less than 1000 for the following queries SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering'); filtered_row_count -------------------- 0 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a < 200'); filtered_row_count -------------------- 801 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a > 200'); filtered_row_count -------------------- 200 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a < 9900'); filtered_row_count -------------------- 101 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a > 9900'); filtered_row_count -------------------- 900 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a < 0'); filtered_row_count -------------------- 0 (1 row) -- Verify that filtered_row_count is less than 2000 for the following queries SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a BETWEEN 1 AND 10'); filtered_row_count -------------------- 990 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a BETWEEN 990 AND 2010'); filtered_row_count -------------------- 1979 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a BETWEEN -10 AND 0'); filtered_row_count -------------------- 0 (1 row) -- Load data for second time and verify that filtered_row_count is exactly twice as before COPY test_block_filtering FROM '@abs_srcdir@/data/block_filtering.csv' WITH CSV; SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a < 200'); filtered_row_count -------------------- 1602 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a < 0'); filtered_row_count -------------------- 0 (1 row) SELECT filtered_row_count('SELECT count(*) FROM test_block_filtering WHERE a BETWEEN 990 AND 2010'); filtered_row_count -------------------- 3958 (1 row) -- Verify that we are fine with collations which use a different alphabet order CREATE FOREIGN TABLE collation_block_filtering_test(A text collate "da_DK") SERVER cstore_server OPTIONS(filename '@abs_srcdir@/data/collation_block_filtering.cstore'); COPY collation_block_filtering_test FROM STDIN; SELECT * FROM collation_block_filtering_test WHERE A > 'B'; a --- Å (1 row)