Обсуждение: Make executor's Range Table an array instead of a List

Поиск
Список
Период
Сортировка

Make executor's Range Table an array instead of a List

От
David Rowley
Дата:
Continuing along on my adventures of making performance improvements
for partitioned tables, I discovered that getrelid is quite slow in
InitPlan() during UPDATEs and DELETEs when there are many
resultRelations to process.  A List is a pretty poor choice for this
data structure since we already know how big it is and we pretty much
only ever perform lookups by the List's Nth element.

Converting this to an array obviously improves that O(n) List lookup
into an O(1) operation.

Benchmark against today's master with 300 partitions using prepared statements:

CREATE TABLE partbench (id BIGINT NOT NULL, i1 INT NOT NULL, i2 INT
NOT NULL, i3 INT NOT NULL, i4 INT NOT NULL, i5 INT NOT NULL) PARTITION
BY RANGE (id);
\o /dev/null
select 'CREATE TABLE partbench' || x::text || ' PARTITION OF partbench
FOR VALUES FROM (' || (x*100000)::text || ') TO (' ||
((x+1)*100000)::text || ');' from generate_Series(0,299) x;
\gexec
\o

ALTER SYSTEM SET plan_cache_mode = 'force_generic_plan'; -- minimise
planning overhead.

update.sql:

\set p_id 29999999
update partbench set i1 = i1+1 where id = :p_id;

pgbench -n -M prepared -T 60 -f update.sql postgres

Unpatched:

tps = 558.708604 (excluding connections establishing)
tps = 556.128341 (excluding connections establishing)
tps = 569.096393 (excluding connections establishing)

Patched:

tps = 683.271356 (excluding connections establishing)
tps = 678.693578 (excluding connections establishing)
tps = 694.446564 (excluding connections establishing)

(about 22% improvement)

Of course, it would be much nicer if PlannedStmt->rtable was already
an array and we didn't have to convert it to one during executor
startup. We'd need to build an array that's a Node type for that to
work.  I did suggest something ([1]) last year in this regard, but I
felt like it might be a bit of an uphill struggle to gain enough
community support to make that work. So I feel like this patch is
quite worthwhile in the meantime.

The (fairly small) patch is attached.

I'll add this to the September 'fest.

[1] https://www.postgresql.org/message-id/flat/CAKJS1f_2SnXhPVa6eWjzy2O9A%3Docwgd0Cj-LQeWpGtrWqbUSDA%40mail.gmail.com

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
Вложения

Re: Make executor's Range Table an array instead of a List

От
Amit Langote
Дата:
(sorry David for the same email twice, I didn't hit Reply All at first)

On Thu, Aug 23, 2018 at 7:58 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> Continuing along on my adventures of making performance improvements
> for partitioned tables, I discovered that getrelid is quite slow in
> InitPlan() during UPDATEs and DELETEs when there are many
> resultRelations to process.  A List is a pretty poor choice for this
> data structure since we already know how big it is and we pretty much
> only ever perform lookups by the List's Nth element.
>
> Converting this to an array obviously improves that O(n) List lookup
> into an O(1) operation.
>
> Benchmark against today's master with 300 partitions using prepared statements:
>
> CREATE TABLE partbench (id BIGINT NOT NULL, i1 INT NOT NULL, i2 INT
> NOT NULL, i3 INT NOT NULL, i4 INT NOT NULL, i5 INT NOT NULL) PARTITION
> BY RANGE (id);
> \o /dev/null
> select 'CREATE TABLE partbench' || x::text || ' PARTITION OF partbench
> FOR VALUES FROM (' || (x*100000)::text || ') TO (' ||
> ((x+1)*100000)::text || ');' from generate_Series(0,299) x;
> \gexec
> \o
>
> ALTER SYSTEM SET plan_cache_mode = 'force_generic_plan'; -- minimise
> planning overhead.
>
> update.sql:
>
> \set p_id 29999999
> update partbench set i1 = i1+1 where id = :p_id;
>
> pgbench -n -M prepared -T 60 -f update.sql postgres
>
> Unpatched:
>
> tps = 558.708604 (excluding connections establishing)
> tps = 556.128341 (excluding connections establishing)
> tps = 569.096393 (excluding connections establishing)
>
> Patched:
>
> tps = 683.271356 (excluding connections establishing)
> tps = 678.693578 (excluding connections establishing)
> tps = 694.446564 (excluding connections establishing)
>
> (about 22% improvement)
>
> Of course, it would be much nicer if PlannedStmt->rtable was already
> an array and we didn't have to convert it to one during executor
> startup. We'd need to build an array that's a Node type for that to
> work.  I did suggest something ([1]) last year in this regard, but I
> felt like it might be a bit of an uphill struggle to gain enough
> community support to make that work. So I feel like this patch is
> quite worthwhile in the meantime.
>
> The (fairly small) patch is attached.

One of the patches I sent last week does the same thing, among a
couple of other things with regard to handling relations in the
executor.  On a cursory look at the patch, your take of it looks
better than mine.  Will test tomorrow.  Here is a link to my email:

https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688@lab.ntt.co.jp

4th of my patches implements the array'fication of executor's range table.

Thanks,
Amit

Re: Make executor's Range Table an array instead of a List

От
David Rowley
Дата:
On 24 August 2018 at 02:26, Amit Langote <amitlangote09@gmail.com> wrote:
> One of the patches I sent last week does the same thing, among a
> couple of other things with regard to handling relations in the
> executor.  On a cursory look at the patch, your take of it looks
> better than mine.  Will test tomorrow.  Here is a link to my email:
>
> https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688@lab.ntt.co.jp
>
> 4th of my patches implements the array'fication of executor's range table.

Sorry, didn't realise. I'll withdraw this and review yours during the
upcoming 'fest.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Make executor's Range Table an array instead of a List

От
Amit Langote
Дата:
On 2018/08/24 7:22, David Rowley wrote:
> On 24 August 2018 at 02:26, Amit Langote <amitlangote09@gmail.com> wrote:
>> One of the patches I sent last week does the same thing, among a
>> couple of other things with regard to handling relations in the
>> executor.  On a cursory look at the patch, your take of it looks
>> better than mine.  Will test tomorrow.  Here is a link to my email:
>>
>> https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688@lab.ntt.co.jp
>>
>> 4th of my patches implements the array'fication of executor's range table.
> 
> Sorry, didn't realise. I'll withdraw this and review yours during the
> upcoming 'fest.

Since your patch implemented the idea in a better manner, I'm thinking I
should merge it with mine.  Is that okay with you?

Thanks,
Amit

Re: Make executor's Range Table an array instead of a List

От
David Rowley
Дата:
On 4 September 2018 at 19:31, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2018/08/24 7:22, David Rowley wrote:
>> On 24 August 2018 at 02:26, Amit Langote <amitlangote09@gmail.com> wrote:
>>> One of the patches I sent last week does the same thing, among a
>>> couple of other things with regard to handling relations in the
>>> executor.  On a cursory look at the patch, your take of it looks
>>> better than mine.  Will test tomorrow.  Here is a link to my email:
>>>
>>> https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688@lab.ntt.co.jp
>>>
>>> 4th of my patches implements the array'fication of executor's range table.
>>
>> Sorry, didn't realise. I'll withdraw this and review yours during the
>> upcoming 'fest.
>
> Since your patch implemented the idea in a better manner, I'm thinking I
> should merge it with mine.  Is that okay with you?

Feel free to do that. I've not yet looked at your patch. I was
planning on looking at your partition planning performance
improvements patches first.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Make executor's Range Table an array instead of a List

От
Amit Langote
Дата:
On 2018/09/04 17:14, David Rowley wrote:
> On 4 September 2018 at 19:31, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> On 2018/08/24 7:22, David Rowley wrote:
>>> On 24 August 2018 at 02:26, Amit Langote <amitlangote09@gmail.com> wrote:
>>>> One of the patches I sent last week does the same thing, among a
>>>> couple of other things with regard to handling relations in the
>>>> executor.  On a cursory look at the patch, your take of it looks
>>>> better than mine.  Will test tomorrow.  Here is a link to my email:
>>>>
>>>> https://www.postgresql.org/message-id/468c85d9-540e-66a2-1dde-fec2b741e688@lab.ntt.co.jp
>>>>
>>>> 4th of my patches implements the array'fication of executor's range table.
>>>
>>> Sorry, didn't realise. I'll withdraw this and review yours during the
>>> upcoming 'fest.
>>
>> Since your patch implemented the idea in a better manner, I'm thinking I
>> should merge it with mine.  Is that okay with you?
> 
> Feel free to do that.

Okay, will do.

> I've not yet looked at your patch. I was
> planning on looking at your partition planning performance
> improvements patches first.

Great, thanks.

Regards,
Amit