I tried to do even something simpler, run the query with only the partition column in the where clause and the results werent good for pg12 :
PG12 :
postgres=# explain analyze select * from iot_data where device=51;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..514086.40 rows=1027284 width=37) (actual time=6.777..61558.272 rows=1010315 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on iot_data_0 (cost=0.00..410358.00 rows=428035 width=37) (actual time=1.152..61414.483 rows=336772 loops=3)
Filter: (device = 51)
Rows Removed by Filter: 9764341
Planning Time: 15.720 ms
Execution Time: 61617.851 ms
(8 rows)
PG9.6
postgres=# explain analyze select * from iot_data where device=51;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Seq Scan on iot_data (cost=0.00..2083334.60 rows=976667 width=37) (actual time=21.922..16753.492 rows=1010315 loops=1)
Filter: (device = 51)
Rows Removed by Filter: 98989685
Planning time: 0.119 ms
Execution time: 16810.787 ms
(5 rows)
Besides hardware, anything else worth checking ? the machine are identical in aspect of resources.
