Re: pg12 partitions show bad performance vs pg96

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(dot)cherkassky(at)gmail(dot)com‬‏>:‬

> 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.
>