Date: Sun, 8 Mar 2020 22:10:05 -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: <20200309031005.GE1357@telsasoft.com>
References: 
 <CA+t6e1mJ+8s6sP4AgxdT5Vv4fXqSt0+qdjvveUHQeJrQugdLQw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+t6e1mJ+8s6sP4AgxdT5Vv4fXqSt0+qdjvveUHQeJrQugdLQw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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