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 table PartTest3 (
	id bigint not null primary key,
	value3 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);
        execute format('create table %I partition of %I for values from (%L) to (%L)',
	                        'parttest3_' || vId, 'parttest3', 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);

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

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

plan.txt

QUERY PLAN                                                                                                                                                  |
------------------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop  (cost=0.84..1353843.78 rows=525000000 width=48) (actual time=24719.777..24722.997 rows=21 loops=1)                                             |
  Buffers: shared hit=129                                                                                                                                   |
  ->  Nested Loop  (cost=0.55..19660.39 rows=105000 width=42) (actual time=24719.514..24721.129 rows=21 loops=1)                                            |
        Buffers: shared hit=66                                                                                                                              |
        ->  Index Scan using parttest1_2001_pkey on parttest1_2001 t1  (cost=0.28..2.89 rows=21 width=20) (actual time=24718.909..24718.917 rows=21 loops=1)|
              Index Cond: ((id >= 2050) AND (id <= 2070))                                                                                                   |
              Buffers: shared hit=3                                                                                                                         |
        ->  Append  (cost=0.28..926.07 rows=1000 width=22) (actual time=0.016..0.020 rows=1 loops=21)                                                       |
              Buffers: shared hit=63                                                                                                                        |
              ->  Index Scan using parttest2_1_pkey on parttest2_1 t2_1  (cost=0.28..0.92 rows=1 width=18) (never executed)                                 |
                    Index Cond: (id = t1.id)                                                                                                                |
              ->  Index Scan using parttest2_1001_pkey on parttest2_1001 t2_2  (cost=0.28..0.92 rows=1 width=20) (never executed)                           |
                    Index Cond: (id = t1.id)                                                                                                                |
              ->  Index Scan using parttest2_2001_pkey on parttest2_2001 t2_3  (cost=0.28..0.92 rows=1 width=20) (actual time=0.014..0.015 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..0.92 rows=1 width=20) (never executed)                           |
                    Index Cond: (id = t1.id)                                                                                                                |
                    
                   ...
                                 ->  Index Scan using parttest3_999001_pkey on parttest3_999001 t3_1000  (cost=0.28..0.92 rows=1 width=22) (never executed)                    |
                    Index Cond: (id = t1.id)                                                                                                                |
Planning Time: 633.599 ms                                                                                                                                   |
JIT:                                                                                                                                                        |
  Functions: 8012                                                                                                                                           |
  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                               |
  Timing: Generation 421.123 ms, Inlining 36.265 ms, Optimization 15028.985 ms, Emission 9621.424 ms, Total 25107.797 ms                                    |
Execution Time: 25157.539 ms                                                                                                                                |

plan2.txt

with jit = off

                    Index Cond: (id = t1.id)                                                                                                               |
              ->  Index Scan using parttest3_999001_pkey on parttest3_999001 t3_1000  (cost=0.28..0.92 rows=1 width=22) (never executed)                   |
                    Index Cond: (id = t1.id)                                                                                                               |
Planning Time: 638.087 ms                                                                                                                                  |
Execution Time: 13.452 ms                                                                                                                                  |