Reply-To: jesper.pedersen@redhat.com
Subject: Re: speeding up planning with partitions
To: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>,
 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>,
 'Amit Langote' <amitlangote09@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, David Rowley
 <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>,
 Robert Haas <robertmhaas@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
References: 
 <CAKJS1f9XAePd2+8F4=wtHKdDdckgKcSoXb-ZbU8Wbt86mhLxKA@mail.gmail.com>
 <b5362c29-cca1-de7b-13d0-9e51f9911185@lab.ntt.co.jp>
 <80afef3f-1870-5cb3-eeaf-7d8ecb41994c@lab.ntt.co.jp>
 <af3da198-e4aa-0535-8b13-40b02a4a7c41@lab.ntt.co.jp>
 <d768d90d-628e-6cdc-513d-f50a0ce02cea@redhat.com>
 <CA+HiwqFTqUVuiyK7dDQDCrNF1NiPDOAnmQum2jcw1yj5Z+kC0Q@mail.gmail.com>
 <0F97FA9ABBDBE54F91744A9B37151A5129DD55@g01jpexmbkw24>
 <256b9c87-b687-6f51-6274-19ae5743aef6@lab.ntt.co.jp>
 <0F97FA9ABBDBE54F91744A9B37151A5129E604@g01jpexmbkw24>
 <9feacaf6-ddb3-96dd-5b98-df5e927b1439@lab.ntt.co.jp>
 <0F97FA9ABBDBE54F91744A9B37151A512B3279@g01jpexmbkw24>
 <0c90ce90-b010-4791-41c0-a0df34f99ffa@lab.ntt.co.jp>
 <0F97FA9ABBDBE54F91744A9B37151A512B3528@g01jpexmbkw24>
 <d964510e-2eec-ce42-8ad8-884caf718e03@lab.ntt.co.jp>
 <0F97FA9ABBDBE54F91744A9B37151A512B359C@g01jpexmbkw24>
 <72094fc9-3232-0fc7-b7cd-42e1c73d5aee@lab.ntt.co.jp>
 <0F97FA9ABBDBE54F91744A9B37151A512B45CD@g01jpexmbkw24>
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Organization: Red Hat
Message-ID: <53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com>
Date: Wed, 20 Mar 2019 10:25:05 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.4.0
MIME-Version: 1.0
In-Reply-To: <0F97FA9ABBDBE54F91744A9B37151A512B45CD@g01jpexmbkw24>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi,

On 3/19/19 11:15 PM, Imai, Yoshikazu wrote:
> Here the details.
> 
> [creating partitioned tables (with 1024 partitions)]
> drop table if exists rt;
> create table rt (a int, b int, c int) partition by range (a);
> \o /dev/null
> select 'create table rt' || x::text || ' partition of rt for values from (' ||
>   (x)::text || ') to (' || (x+1)::text || ');' from generate_series(1, 1024) x;
> \gexec
> \o
> 
> [select1024.sql]
> \set a random (1, 1024)
> select * from rt where a = :a;
> 
> [pgbench]
> pgbench -n -f select1024.sql -T 60
> 
> 

My tests - using hash partitions - shows that the extra time is spent in 
make_partition_pruneinfo() for the relid_subplan_map variable.

   64 partitions: make_partition_pruneinfo() 2.52%
8192 partitions: make_partition_pruneinfo() 5.43%

TPS goes down ~8% between the two. The test setup is similar to the above.

Given that Tom is planning to change the List implementation [1] in 13 I 
think using the palloc0 structure is ok for 12 before trying other 
implementation options.

perf sent off-list.

[1] https://www.postgresql.org/message-id/24783.1551568303%40sss.pgh.pa.us

Best regards,
  Jesper