RE: speeding up planning with partitions - Mailing list pgsql-hackers

Hi, David

On Thu, Mar 14, 2019 at 9:04 AM, David Rowley wrote:
> On Thu, 14 Mar 2019 at 21:35, Imai, Yoshikazu
> <imai.yoshikazu@jp.fujitsu.com> wrote:
> > 0007:
> > * This changes some processes using "for loop" to using
> "while(bms_next_member())" which speeds up processing when we scan few
> partitions in one statement, but when we scan a lot of partitions in one
> statement, its performance will likely degraded. I measured the
> performance of both cases.
> > I executed select statement to the table which has 4096 partitions.
> >
> > [scanning 1 partition]
> > Without 0007 : 3,450 TPS
> > With 0007    : 3,723 TPS
> >
> > [scanning 4096 partitions]
> > Without 0007 : 10.8 TPS
> > With 0007    : 10.5 TPS
> >
> > In the above result, performance degrades 3% in case of scanning 4096
> partitions compared before and after applying 0007 patch. I think when
> scanning a lot of tables, executor time would be also longer, so the
> increasement of planner time would be relatively smaller than it. So we
> might not have to care this performance degradation.
> 
> I think it's better to focus on the fewer partitions case due to the fact
> that execution initialisation time and actual execution are likely to
> take much longer when more partitions are scanned.  I did some work on
> run-time pruning to tune it for this case.  Tom did make a similar argument
> in [1] and I explained my reasoning in [2].

Thanks for quoting these threads.
Actually, I recalled this argument, so I tested this just to make sure. 

> bms_next_member has gotten a good performance boost since then and the
> cases are not exactly the same since the old version the loop in run-time
> pruning checked bms_is_member(), but the fact is, we did end up tuning
> for the few partitions case in the end.

Wow, I didn't know that.

> However, it would be good to see the performance results for
> plan+execution time of say a table with 4k parts looking up a single
> indexed value.  You could have two columns, one that's the partition key
> which allows the pruning to take place, and one that's not and results
> in scanning all partitions. I'll be surprised if you even notice the
> difference between with and without 0007 with the latter case.

So I tested for checking the performance for plan+execution time.

[set up table and indexes]
create table rt (a int, b 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, 4096) x;
\gexec
\o

create index b_idx on rt (b);

insert into rt select a, b from generate_series(1, 4096) a, generate_series(1, 1000) b;

[select_indexed_values.sql]
\set b random(1, 1000)
select count(*) from rt where b = :b;

[pgbench]
pgbench -n -f select_indexed_values.sql -T 60 postgres

[results]
Without 0007: 3.18 TPS (3.25, 3.13, 3.15)
With 0007:    3.21 TPS (3.21, 3.23, 3.18)

From the results, we didn't see the performance degradation in this case. Actually, the performance increased 1% before
andafter applying 0007, but it would be just an measurement error.
 
So, generally, we can think the performance difference of bms_next_member and for loop can be absorbed by other
processing(executioninitialisation and actual execution) when scanning many partitions.
 

--
Yoshikazu Imai