Date: Mon, 9 Mar 2020 07:12:13 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: pg12 partitions show bad performance vs pg96
Message-ID: <20200309121212.GF1357@telsasoft.com>
References: 
 <CA+t6e1mJ+8s6sP4AgxdT5Vv4fXqSt0+qdjvveUHQeJrQugdLQw@mail.gmail.com>
 <CAApHDvq=Kn2JK1mg==zenQYhoFpxBE=g6=qy_v2P3tdrV2nNBQ@mail.gmail.com>
 <CA+t6e1mx_KiR735zbPFmkG-HBmLOOGRX3-ZMWNok7F-ieTdB3A@mail.gmail.com>
 <CA+t6e1nXcPB37M3qTkBCcaQoALUsfr7=YzAHWajLn4vRhoPLHw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+t6e1nXcPB37M3qTkBCcaQoALUsfr7=YzAHWajLn4vRhoPLHw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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