Re: speeding up planning with partitions

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