Re: pg12 partitions show bad performance vs pg96

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