On Mon, Mar 09, 2020 at 12:31:15PM +0200, Mariel Cherkassky wrote: > > 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) For whatever reason, your storage/OS seem to be handling parallel reads poorly. I would SET max_parallel_workers_per_gather=0 and retest (but also look into improving the storage). Also, it's not required, but I think a typical partitioning schema would have an index on the column being partitioned. I see you have an index on iot_data(metadata,lower(data)), so I still wonder whether you'd have better results partitioned on metadata, or otherwise maybe adding an index on "device". But I don't know what your typical queries are. > 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) -- Justin
