benchmark.sql

drop table PartTest1;
drop table PartTest2;

create table PartTest1 (
	id bigint not null primary key,
	value1 text
) partition by range (id);

create table PartTest2 (
	id bigint not null primary key,
	value2 text
) partition by range (id);

create or replace procedure createTestPartitions() as $$
declare
	vId bigint := 1;
	vNextId bigint;
	cPartitionSize constant bigint := 1000;
	cNumberOfPartitions constant bigint := 1000;
begin
	while vId <= cPartitionSize * cNumberOfPartitions loop
		vNextId := vId + cPartitionSize;
		execute format('create table %I partition of %I for values from (%L) to (%L)',
	                        'parttest1_' || vId, 'parttest1', vId, vNextId);
		execute format('create table %I partition of %I for values from (%L) to (%L)',
	                        'parttest2_' || vId, 'parttest2', vId, vNextId);
        vId := vNextId;
	end loop;
end;
$$ language plpgsql;

call createTestPartitions();

insert into PartTest1 (id, value1)
select i, 'value1 ' || i
from generate_series(1, 1000*1000) g(i);

insert into PartTest2 (id, value2)
select i, 'value2 ' || i
from generate_series(1, 1000*1000) g(i);

explain (analyze, buffers)
select t2.id, t1.value1, t2.value2
from PartTest1 t1 inner join PartTest2 t2 on t1.id = t2.id
where t1.id between 2050 and 2070;

plan.txt

QUERY PLAN                                                                                                                                           |
-----------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop  (cost=0.56..11767.00 rows=36000 width=72) (actual time=0.249..1.762 rows=21 loops=1)                                                    |
  Buffers: shared hit=66                                                                                                                             |
  ->  Index Scan using parttest1_2001_pkey on parttest1_2001 t1  (cost=0.28..7.00 rows=6 width=40) (actual time=0.018..0.025 rows=21 loops=1)        |
        Index Cond: ((id >= 2050) AND (id <= 2070))                                                                                                  |
        Buffers: shared hit=3                                                                                                                        |
  ->  Append  (cost=0.28..1950.00 rows=1000 width=40) (actual time=0.003..0.005 rows=1 loops=21)                                                     |
        Buffers: shared hit=63                                                                                                                       |
        ->  Index Scan using parttest2_1_pkey on parttest2_1 t2_1  (cost=0.28..1.94 rows=1 width=40) (never executed)                                |
              Index Cond: (id = t1.id)                                                                                                               |
        ->  Index Scan using parttest2_1001_pkey on parttest2_1001 t2_2  (cost=0.28..1.94 rows=1 width=40) (never executed)                          |
              Index Cond: (id = t1.id)                                                                                                               |
        ->  Index Scan using parttest2_2001_pkey on parttest2_2001 t2_3  (cost=0.28..1.94 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=21)|
              Index Cond: (id = t1.id)                                                                                                               |
              Buffers: shared hit=63                                                                                                                 |
        ->  Index Scan using parttest2_3001_pkey on parttest2_3001 t2_4  (cost=0.28..1.94 rows=1 width=40) (never executed)                          |
              Index Cond: (id = t1.id)                                                                                                               |
        ->  Index Scan using parttest2_4001_pkey on parttest2_4001 t2_5  (cost=0.28..1.94 rows=1 width=40) (never executed)                          |
              Index Cond: (id = t1.id)                                                                                                               |
...
        ->  Index Scan using parttest2_999001_pkey on parttest2_999001 t2_1000  (cost=0.28..1.94 rows=1 width=40) (never executed)                   |
              Index Cond: (id = t1.id)                                                                                                               |
Planning Time: 89.011 ms                                                                                                                             |
Execution Time: 10.704 ms                                                                                                                            |