Postgres planner nodes demo

nodes.sql

SET maintenance_work_mem TO '1GB';

CREATE TABLE t_test (a int NOT NULL, b int, c text);
INSERT INTO t_test SELECT i AS a, random()*100 as b, random() as c FROM generate_series(1, 2*1e6::int) AS i;
CREATE INDEX ON t_test (a);
ANALYZE t_test;

CREATE TABLE t_joiny (a INT NOT NULL, b int);
INSERT INTO t_joiny SELECT i AS a, (random()*100)::int as b FROM generate_series(1, 1e6::int) AS i ;
CREATE UNIQUE INDEX ON t_joiny (a);
ANALYZE t_joiny;



-- Seq Scan
EXPLAIN SELECT * FROM t_test;

-- Index Scan
EXPLAIN SELECT * FROM t_test WHERE a = 0;

-- Index Only Scan
EXPLAIN SELECT a FROM t_test WHERE a = 0;

-- Bitmap Index/Heap Scan
set enable_seqscan to off;
explain analyze SELECT * FROM t_test WHERE b > 96 or b < 3;
set enable_seqscan to on;

-- Tid Scan
explain SELECT * FROM t_test WHERE ctid = '(100, 1)';

-- CTE Scan
EXPLAIN WITH q_sub AS (select * FROM t_test limit 1000) SELECT * FROM q_sub;

-- Materialize
EXPLAIN SELECT * FROM (select * FROM t_test limit 100) a, t_joiny;

-- Values Scan
EXPLAIN SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);



-- Merge JOIN with Index/Index Only Scan
EXPLAIN SELECT t_test.* FROM t_test JOIN t_joiny using (a) WHERE t_test.b < 50;

-- Nested Loop with Index/Index Only Scan.
EXPLAIN SELECT t_test.* FROM t_test JOIN t_joiny using (a) WHERE t_test.a < 2000 ;

-- Nested loop. Always used for CROSS JOIN
EXPLAIN SELECT t_test.* FROM t_test CROSS JOIN t_joiny;

-- Hash JOIN with Seq Scan
EXPLAIN SELECT * FROM t_test JOIN t_joiny using (a) WHERE t_test.b < 2;

-- Anti Join
-- Hash
explain select * from t_test t where not exists (select * from t_joiny j where t.a = j.a);
-- Merge
explain select * from t_test t where not exists (select * from t_joiny j where t.a = j.a) limit 100;


-- Semi Join
explain select * from t_test t where exists (select * from t_joiny j where t.b = j.a);

-- Using index for sorting
EXPLAIN analyze SELECT * FROM t_joiny order by a limit 100;

-- Quick sort completely in memory
EXPLAIN analyze SELECT * FROM (select * FROM t_test limit 1000) a order by random();

-- top-N heapsort in memory
EXPLAIN analyze SELECT * FROM t_joiny order by b limit 100;

-- Disk merge sort
EXPLAIN analyze SELECT * FROM t_test order by random();



-- HashAggregate in memory
EXPLAIN SELECT b, count(*) FROM t_test group by b;

-- Append
EXPLAIN SELECT 1 as a union all SELECT 2;

-- Unique
EXPLAIN SELECT 1 as a union all SELECT 2;

--HashSetOp (Except,Intersect)
EXPLAIN SELECT b FROM t_test EXCEPT SELECT b FROM t_joiny;