I realized that the planner goes to the right partition because "(never executed)" is mentioned near the scan of the other partitions. However, still i'm not sure why performance is better in pg96.
בתאריך יום א׳, 8 במרץ 2020 ב-18:05 מאת 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 6pg 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 = 8040MBpostgresql12.2I 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.dumppsql -d postgres -U postgres -f -h pg12_machine /tmp/iot_data.dumppostgres=# 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.
