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.
