Re: speeding up planning with partitions

Hi Jesper,

On 2019/03/20 23:25, Jesper Pedersen wrote:> 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
>
>

Thanks for testing.
Yeah, after code looking, I think bottleneck seems be lurking in another
place where this patch doesn't change. I also think the patch is ok as
it is for 12, and this problem will be fixed in 13.

--
Yoshikazu Imai