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
Date: Mon, 9 Mar 2020 07:12:13 -0500
Message-ID: <20200309121212.GF1357@telsasoft.com> (raw)
In-Reply-To: <CA+t6e1nXcPB37M3qTkBCcaQoALUsfr7=YzAHWajLn4vRhoPLHw@mail.gmail.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, mariel.cherkassky@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: pg12 partitions show bad performance vs pg96
In-Reply-To: <20200309121212.GF1357@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox