pg12 partitions show bad performance vs pg96

[Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx>]

Hey,

I upgraded from 96 to 12 in our test env and I'm seeing that for queries that involve join operation between a partition table and other tables there is degradation is performance compared to pg96 performance.

My machine : 8cpu,16gb,regular hd,linux redhat 6

pg settings : 

max_wal_size = 2GB
min_wal_size = 1GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min
log_checkpoints = on
log_lock_waits = on
log_temp_files = 1024
log_min_duration_statement = 10000
log_autovacuum_min_duration = 10000
standard_conforming_strings = off
max_locks_per_transaction = 5000
max_connections = 500
log_line_prefix = '%t %d %p  '
random_page_cost = 4
deadlock_timeout = 5s
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 32764
log_directory = 'pg_log'
enable_partitionwise_join = on # for pg12
enable_partitionwise_aggregate = on  # for pg12
listen_addresses =  '*'
ssl =  on
maintenance_work_mem = 333MB
work_mem = 16MB
shared_buffers = 4020MB
effective_cache_size = 8040MB

postgresql12.2

I used this table as the joined table for both cases : 

 create table iot_device(id serial primary key,name text);

insert into iot_device(name) select generate_series(1,100)||'a';    

In pg96 I created the following regular table : 

create table iot_data(id serial primary key,data text,metadata bigint,device bigint references iot_device(id));

inserted the data : 

  insert into iot_data select generate_series(1,10000000),random()*10,random()*254,random()*99+1;  

In pg12 I created  a table with 3 hash partitiones : 

create table iot_data(id serial ,data text,metadata bigint,device bigint references iot_device(id),primary key(id,device)) partition by hash(device);
create table iot_data_0 partition of iot_data for values with (MODULUS 3, remainder 0);
create table iot_data_1 partition of iot_data for values with (MODULUS 3, remainder 1);
create table iot_data_2 partition of iot_data for values with (MODULUS 3, remainder 2);

I generated a dump of the data in the pg96 machine and inserted it into the pg12 db : 

pg_dump -d postgres -U postgres -a -t iot_data > iot_data.dump

psql -d postgres -U postgres -f  -h pg12_machine /tmp/iot_data.dump

postgres=# select count(*) from iot_data_0;
  count
---------
 3028682
(1 row)

postgres =# select count(*) from iot_data_1;
  count
---------
 3234335
(1 row)

postgres =# select count(*) from iot_data_2;
  count
---------
 3736983
(1 row)

create index on iot_data(metadata,lower(data));

vacuum analyze iot_data;

and now for the performance:

query :  explain analyze select * from iot_data da,iot_device de where de.name in ('50a') and de.id = da.device and da.metadata=50 and lower(da.data) like '50';  

PG12 : 

                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.16..773.61 rows=2 width=43) (actual time=2.858..2.858 rows=0 loops=1)
   ->  Seq Scan on iot_device de  (cost=0.00..2.25 rows=1 width=7) (actual time=0.014..0.020 rows=1 loops=1)
         Filter: (name = '50a'::text)
         Rows Removed by Filter: 99
   ->  Append  (cost=5.16..771.30 rows=6 width=36) (actual time=2.835..2.835 rows=0 loops=1)
         ->  Bitmap Heap Scan on iot_data_0 da  (cost=5.16..233.78 rows=2 width=36) (actual time=2.829..2.829 rows=0 loops=1)
               Recheck Cond: (metadata = 50)
               Filter: ((de.id = device) AND (lower(data) ~~ '50'::text))
               ->  Bitmap Index Scan on iot_data_0_metadata_lower_idx  (cost=0.00..5.14 rows=59 width=0) (actual time=2.827..2.827 rows=0 loops=1)
                     Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
         ->  Bitmap Heap Scan on iot_data_1 da_1  (cost=5.20..249.32 rows=2 width=37) (never executed)
               Recheck Cond: (metadata = 50)
               Filter: ((de.id = device) AND (lower(data) ~~ '50'::text))
               ->  Bitmap Index Scan on iot_data_1_metadata_lower_idx  (cost=0.00..5.18 rows=63 width=0) (never executed)
                     Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
         ->  Bitmap Heap Scan on iot_data_2 da_2  (cost=5.30..288.16 rows=2 width=36) (never executed)
               Recheck Cond: (metadata = 50)
               Filter: ((de.id = device) AND (lower(data) ~~ '50'::text))
               ->  Bitmap Index Scan on iot_data_2_metadata_lower_idx  (cost=0.00..5.29 rows=73 width=0) (never executed)
                     Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
 Planning Time: 8.157 ms
 Execution Time: 2.920 ms
(22 rows)

PG96 : 

                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=6.57..397.19 rows=2 width=44) (actual time=0.121..0.121 rows=0 loops=1)
   Join Filter: (da.device = de.id)
   ->  Seq Scan on iot_device de  (cost=0.00..2.25 rows=1 width=7) (actual time=0.016..0.022 rows=1 loops=1)
         Filter: (name = '50a'::text)
         Rows Removed by Filter: 99
   ->  Bitmap Heap Scan on iot_data da  (cost=6.57..392.49 rows=196 width=37) (actual time=0.097..0.097 rows=0 loops=1)
         Recheck Cond: (metadata = 50)
         Filter: (lower(data) ~~ '50'::text)
         ->  Bitmap Index Scan on iot_data_metadata_lower_idx  (cost=0.00..6.52 rows=196 width=0) (actual time=0.095..0.095 rows=0 loops=1)
               Index Cond: ((metadata = 50) AND (lower(data) = '50'::text))
 Planning time: 0.815 ms
 Execution time: 0.158 ms
(12 rows)

As you can see, the results are better in pg96. This example only shows the results for a small data set. In bigger data sets I get a bigger diff...

I tried changing many postgresql.conf parameters that were added (max_workers_per_gather,enable_partitionwise_join and so on..).

I dont understand why in pg12 it scans all the partitions instead of the relevant one..

I added all the commands to recreate the test, please feel free to share any useful notes.