explaintesti

text/plain

Download

BEGIN;

CREATE TABLE foo(a int, b int);
CREATE TABLE bar(a int, b int);

CREATE TABLE big_table(a int, b int);

INSERT INTO big_table SELECT i%3, i FROM generate_series(1, 200000) i;

INSERT INTO foo SELECT i, i FROM generate_series(1, 10) i;
INSERT INTO bar SELECT i, i FROM generate_series(1, 10) i;

CREATE INDEX foo_a_index ON big_table(a);

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE passwd (username text, pass text, uid int8, gid int8, gecos text, home text, shell text) SERVER file_server OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');

CREATE FUNCTION is_even(int) RETURNS boolean AS $$ BEGIN RETURN ($1 % 2) = 0; END $$ LANGUAGE plpgsql;

SET enable_bitmapscan TO false;
SET enable_hashjoin TO false;
SET enable_indexscan TO false;
SET enable_mergejoin TO false;
SET enable_seqscan TO false;
SET enable_nestloop TO false;
SET enable_hashagg TO false;
SET enable_material TO false;

-- Filter on Index Scan
SET enable_indexscan TO true;
EXPLAIN ANALYZE SELECT * FROM big_table WHERE a=2 AND b > 5000;
SET enable_indexscan TO false;

-- Recheck Cond and Filter on Bitmap Heap Scan
SET enable_bitmapscan TO true;
SET work_mem TO '64kB';
EXPLAIN ANALYZE SELECT * FROM big_table WHERE a=2 AND b > 5000;
RESET work_mem;
SET enable_bitmapscan TO false;

-- no ugly costs for the rest of the tests
SET enable_seqscan TO true;

-- Filter on Sequential Scan
EXPLAIN ANALYZE SELECT * FROM foo WHERE a > 5;

-- Filter on VALUES
EXPLAIN ANALYZE SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) ss(i) WHERE i > 5;

-- Filter on CTE Scan and WorkTable Scan
EXPLAIN ANALYZE WITH RECURSIVE t(a) AS (SELECT i FROM generate_series(1,5) i UNION ALL SELECT a+5 FROM t WHERE a+5 <= 10) SELECT * FROM t WHERE a > 5;

-- Filter on SubqueryScan
EXPLAIN ANALYZE SELECT * FROM (TABLE foo OFFSET 0) ss(i) WHERE i > 5;

-- Filter on Function Scan
EXPLAIN ANALYZE SELECT * FROM generate_series(1, 10) i WHERE i > 5;

-- Filter on Tid Scan
EXPLAIN ANALYZE SELECT * FROM big_table WHERE ctid = '(5,2)' AND a = 0;

-- Filter on Foreign Scan
EXPLAIN ANALYZE SELECT * FROM passwd WHERE uid > 4;


SET enable_nestloop TO true;

-- Filter on Nested Loop Join
EXPLAIN ANALYZE SELECT * FROM foo LEFT JOIN bar USING (a) WHERE is_even(bar.a);

-- Sane answers even when a node with Filter is called repeatedly
EXPLAIN ANALYZE SELECT * FROM foo LEFT JOIN (SELECT * FROM bar WHERE a > 5) bar USING (a);

SET enable_nestloop TO false;


-- Filter on Merge Join
SET enable_mergejoin TO true;
EXPLAIN ANALYZE SELECT * FROM foo LEFT JOIN bar USING (a) WHERE is_even(bar.a);
SET enable_mergejoin TO false;

-- Filter on Hash Join
SET enable_hashjoin TO true;
EXPLAIN ANALYZE SELECT * FROM foo LEFT JOIN bar USING (a) WHERE is_even(bar.a);
SET enable_hashjoin TO false;

-- Filter on Group
EXPLAIN ANALYZE SELECT * FROM (SELECT a, b FROM foo GROUP BY 1, 2) ss WHERE is_even(a);

-- Filter on (Hash) Aggregate
SET enable_hashagg TO true;
EXPLAIN ANALYZE SELECT * FROM (SELECT a, b FROM foo GROUP BY 1, 2) ss WHERE is_even(a);
SET enable_hashagg TO false;


ROLLBACK;