\set VERBOSITY terse SET search_path = 'public'; CREATE SCHEMA pathman; CREATE EXTENSION pg_pathman SCHEMA pathman; CREATE SCHEMA test; CREATE TABLE test.range_rel ( id SERIAL PRIMARY KEY, dt TIMESTAMP NOT NULL, txt TEXT); CREATE INDEX ON test.range_rel (dt); INSERT INTO test.range_rel (dt, txt) SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g; SELECT pathman.create_range_partitions('test.range_rel', 'DT', '2015-01-01'::DATE, '1 month'::INTERVAL); create_range_partitions ------------------------- 4 (1 row) CREATE TABLE test.num_range_rel ( id SERIAL PRIMARY KEY, txt TEXT); SELECT pathman.create_range_partitions('test.num_range_rel', 'id', 0, 1000, 4); create_range_partitions ------------------------- 4 (1 row) INSERT INTO test.num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g; SET pg_pathman.enable_runtimeappend = OFF; SET pg_pathman.enable_runtimemergeappend = OFF; VACUUM; /* * Hash join */ SET enable_indexscan = ON; SET enable_seqscan = OFF; SET enable_nestloop = OFF; SET enable_hashjoin = ON; SET enable_mergejoin = OFF; EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel j1 JOIN test.range_rel j2 on j2.id = j1.id JOIN test.num_range_rel j3 on j3.id = j1.id WHERE j1.dt < '2015-03-01' AND j2.dt >= '2015-02-01' ORDER BY j2.dt; QUERY PLAN ------------------------------------------------------------------------------------------- Sort Sort Key: j2.dt -> Hash Join Hash Cond: (j3.id = j2.id) -> Append -> Index Scan using num_range_rel_1_pkey on num_range_rel_1 j3 -> Index Scan using num_range_rel_2_pkey on num_range_rel_2 j3_1 -> Index Scan using num_range_rel_3_pkey on num_range_rel_3 j3_2 -> Index Scan using num_range_rel_4_pkey on num_range_rel_4 j3_3 -> Hash -> Hash Join Hash Cond: (j2.id = j1.id) -> Append -> Index Scan using range_rel_2_dt_idx on range_rel_2 j2 -> Index Scan using range_rel_3_dt_idx on range_rel_3 j2_1 -> Index Scan using range_rel_4_dt_idx on range_rel_4 j2_2 -> Hash -> Append -> Index Scan using range_rel_1_pkey on range_rel_1 j1 -> Index Scan using range_rel_2_pkey on range_rel_2 j1_1 (20 rows) DROP SCHEMA test CASCADE; NOTICE: drop cascades to 12 other objects DROP EXTENSION pg_pathman CASCADE; DROP SCHEMA pathman CASCADE;