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: Sun, 8 Mar 2020 22:10:05 -0500
Message-ID: <20200309031005.GE1357@telsasoft.com> (raw)
In-Reply-To: <CA+t6e1mJ+8s6sP4AgxdT5Vv4fXqSt0+qdjvveUHQeJrQugdLQw@mail.gmail.com>
References: <CA+t6e1mJ+8s6sP4AgxdT5Vv4fXqSt0+qdjvveUHQeJrQugdLQw@mail.gmail.com>
On Sun, Mar 08, 2020 at 06:05:26PM +0200, Mariel Cherkassky wrote:
> In pg12 I created a table with 3 hash partitiones :
> create table iot_data(id serial ,data text,metadata bigint,device bigint
> references iot_device(id),primary key(id,device)) partition by hash(device);
> and now for the performance:
> query : explain analyze select * from iot_data da,iot_device de where
> de.name in ('50a') and de.id = da.device and da.metadata=50 and
> lower(da.data) like '50';
> I dont understand why in pg12 it scans all the partitions instead of the
> relevant one..
As you noticed, it doesn't actually scan them. I believe v11 "partition
elimination during query execution" is coming into play here. There's no
option to disable that, but as a quick test, you could possibly try under PG10
(note, that doesn't support inherited indexes). Or you could try to profile
under PG12 (and consider comparing with pg13dev).
You partitioned on hash(iot_data.device), but your query doesn't specify
device, except that da.device=de.id AND de.name IN ('50'). If that's a typical
query, maybe it'd work better to partition on metadata or lower(name) (or
possibly both).
On Sun, Mar 08, 2020 at 06:05:26PM +0200, Mariel Cherkassky wrote:
> PG12 :
> Nested Loop (cost=5.16..773.61 rows=2 width=43) (actual time=2.858..2.858
> rows=0 loops=1)
...
> -> Bitmap Heap Scan on iot_data_1 da_1 (cost=5.20..249.32 rows=2
> width=37) (NEVER EXECUTED)
...
> Planning Time: 8.157 ms
> Execution Time: 2.920 ms
> PG96 :
> Nested Loop (cost=6.57..397.19 rows=2 width=44) (actual time=0.121..0.121
> rows=0 loops=1)
...
> Planning time: 0.815 ms
> Execution time: 0.158 ms
--
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: <20200309031005.GE1357@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