Re: Optimizing ResouceOwner to speed up COPY - Mailing list pgsql-hackers
| From | Tomas Vondra |
|---|---|
| Subject | Re: Optimizing ResouceOwner to speed up COPY |
| Date | |
| Msg-id | 708057fe-2c6a-4697-96d7-052b2868bc9e@vondra.me Whole thread Raw |
| In response to | Re: Optimizing ResouceOwner to speed up COPY (Heikki Linnakangas <hlinnaka@iki.fi>) |
| List | pgsql-hackers |
On 10/21/25 09:10, Heikki Linnakangas wrote:
> On 18/10/2025 01:49, Tomas Vondra wrote:
>> On 10/17/25 12:32, Tomas Vondra wrote:
>>>
>>>
>>> On 10/17/25 10:31, Heikki Linnakangas wrote:
>>>>> typedef struct ResourceElem
>>>>> {
>>>>> Datum item;
>>>>> + uint32 count; /* number of occurrences */
>>>>> const ResourceOwnerDesc *kind; /* NULL indicates a free hash
>>>>> table slot */
>>>>> } ResourceElem;
>>>>
>>>> Hmm, the 'count' is not used when the entry is stored in the array.
>>>> Perhaps we should have a separate struct for array and hash elements
>>>> now. Keeping the array small helps it to fit in CPU caches.
>>>
>>> Agreed. I had the same idea yesterday, but I haven't done it yet.
>>
>> The attached v2 does that - it adds a separate ResourceHashElem for the
>> has table, and it works. But I'm not sure I like it very much, because
>> there are two places that relied on both the array and hash table using
>> the same struct to "walk" it the same way.
>>
>> For ResourceOwnerSort() it's not too bad, but ResourceOwnerReleaseAll()
>> now duplicates most of the code. It's not terrible, but also not pretty.
>> I can't think of a better way, though.
>
> Looks fine to me. The code duplication is not too bad IMO.
>
> Here's a rebased version of the micro-benchmark I used when I was
> working on the ResourceOwner refactoring (https://www.postgresql.org/
> message-id/d746cead-a1ef-7efe-fb47-933311e876a3%40iki.fi).
>
> I ran it again on my laptop. Different from the one I used back then, so
> the results are not comparable with the results from that old thread.
>
> Unpatched (commit 18d26140934):
>
> postgres=# \i contrib/resownerbench/snaptest.sql
> numkeep | numsnaps | lifo_time_ns | fifo_time_ns
> ---------+----------+--------------+--------------
> 0 | 1 | 11.6 | 11.1
> 0 | 5 | 12.1 | 13.1
> 0 | 10 | 12.3 | 13.5
> 0 | 60 | 14.6 | 19.4
> 0 | 70 | 16.0 | 18.1
> 0 | 100 | 16.7 | 18.0
> 0 | 1000 | 18.1 | 20.7
> 0 | 10000 | 21.9 | 29.5
> 9 | 10 | 11.0 | 11.1
> 9 | 100 | 14.9 | 20.0
> 9 | 1000 | 16.1 | 24.4
> 9 | 10000 | 21.9 | 25.7
> 65 | 70 | 11.7 | 12.5
> 65 | 100 | 13.9 | 14.8
> 65 | 1000 | 16.7 | 17.8
> 65 | 10000 | 22.5 | 27.8
> (16 rows)
>
> v2-0001-Deduplicate-entries-in-ResourceOwner.patch:
>
> postgres=# \i contrib/resownerbench/snaptest.sql
> numkeep | numsnaps | lifo_time_ns | fifo_time_ns
> ---------+----------+--------------+--------------
> 0 | 1 | 10.8 | 10.6
> 0 | 5 | 11.5 | 12.3
> 0 | 10 | 12.1 | 13.0
> 0 | 60 | 13.9 | 19.4
> 0 | 70 | 15.9 | 18.7
> 0 | 100 | 16.0 | 18.5
> 0 | 1000 | 19.2 | 21.6
> 0 | 10000 | 22.4 | 29.0
> 9 | 10 | 11.2 | 11.3
> 9 | 100 | 14.4 | 19.9
> 9 | 1000 | 16.4 | 23.8
> 9 | 10000 | 22.4 | 25.7
> 65 | 70 | 11.4 | 12.1
> 65 | 100 | 14.8 | 17.0
> 65 | 1000 | 16.9 | 18.1
> 65 | 10000 | 22.5 | 28.5
> (16 rows)
>
> v20251016-0001-Deduplicate-entries-in-ResourceOwner.patch:
>
> postgres=# \i contrib/resownerbench/snaptest.sql
> numkeep | numsnaps | lifo_time_ns | fifo_time_ns
> ---------+----------+--------------+--------------
> 0 | 1 | 11.3 | 11.1
> 0 | 5 | 12.3 | 13.0
> 0 | 10 | 13.0 | 14.1
> 0 | 60 | 14.7 | 20.5
> 0 | 70 | 16.3 | 19.0
> 0 | 100 | 16.5 | 18.4
> 0 | 1000 | 19.0 | 22.4
> 0 | 10000 | 23.2 | 29.6
> 9 | 10 | 11.2 | 11.1
> 9 | 100 | 14.8 | 20.5
> 9 | 1000 | 16.8 | 24.3
> 9 | 10000 | 23.3 | 26.5
> 65 | 70 | 12.4 | 13.0
> 65 | 100 | 15.2 | 16.6
> 65 | 1000 | 16.9 | 18.4
> 65 | 10000 | 23.4 | 29.3
> (16 rows)
>
> These are just a single run on my laptop, the error bars on individual
> numbers are significant. But it seems to me that V2 is maybe a little
> faster when the entries fit in the array.
>
Thanks. Attached is a version that adds a missing .sql file defining the
benchmark functions, and then two patches. The v2 is the patch already
shared on Saturday, v3 is a minor tweak (details in a minute).
I ran the benchmark on my test machine with both v1 and v2, with 10
runs. And there seems to be a small regression of ~5-10% (compared to
master). Which is not terrible, but also not negligible. v0 is master.
Here's the "fifo" results:
keep snaps | v0 v1 v2 v3 | v1 v2 v3
==================================================================
0 1 | 10.57 10.70 10.73 10.5 | 101% 102% 99%
5 | 10.52 10.70 10.90 10.5 | 102% 104% 100%
10 | 11.41 11.90 11.94 11.5 | 104% 105% 101%
60 | 15.04 15.74 15.82 15.5 | 105% 105% 103%
70 | 13.73 14.42 14.80 14.3 | 105% 108% 104%
100 | 13.65 14.27 14.59 14.1 | 105% 107% 104%
1000 | 15.07 15.78 16.27 15.6 | 105% 108% 104%
10000 | 23.15 24.96 25.57 24.1 | 108% 110% 104%
------------------------------------------------------------------
9 10 | 10.8 10.94 10.86 10.6 | 101% 101% 98%
100 | 15.83 16.35 16.72 16.1 | 103% 106% 102%
1000 | 19.04 19.70 20.34 19.6 | 103% 107% 103%
10000 | 21.5 23.37 24.18 22.5 | 109% 112% 105%
------------------------------------------------------------------
65 70 | 10.58 10.95 11.18 10.6 | 103% 106% 100%
100 | 13.29 14.28 14.73 14.1 | 107% 111% 106%
1000 | 14.62 15.49 15.99 15.2 | 106% 109% 104%
10000 | 22.98 24.78 25.55 24.3 | 108% 111% 106%
and here's "lifo"
keep snaps | v0 v1 v2 v3 | v1 v2 v3
==================================================================
0 1 | 10.73 10.74 11.06 10.4 | 100% 103% 97%
5 | 10.44 10.45 10.62 10.2 | 100% 102% 98%
10 | 11.82 11.84 12.17 11.6 | 100% 103% 98%
60 | 12.15 12.81 12.94 12.4 | 105% 107% 102%
70 | 12.84 13.61 13.95 13.4 | 106% 109% 104%
100 | 12.98 13.73 14.06 13.5 | 106% 108% 104%
1000 | 13.71 14.56 14.80 14.2 | 106% 108% 104%
10000 | 17.68 19.51 20.38 19.0 | 110% 115% 108%
------------------------------------------------------------------
9 10 | 10.96 10.92 11.02 10.6 | 100% 101% 97%
100 | 12.58 13.11 13.26 12.8 | 104% 105% 102%
1000 | 13.67 14.38 14.73 14.1 | 105% 108% 103%
10000 | 17.67 19.79 20.20 19.2 | 112% 114% 109%
------------------------------------------------------------------
65 70 | 10.58 10.85 10.78 11.1 | 103% 102% 105%
100 | 13.03 14.12 14.27 13.5 | 108% 110% 103%
1000 | 13.65 14.56 14.88 14.1 | 107% 109% 104%
10000 | 17.62 19.61 20.30 19.0 | 111% 115% 108%
I was wondering where the regression comes from, and it occurred to me
ResourceOwnerAddToHash() may be doing the checks in the wrong order. It
should be checking for empty slot first, so I did that - that's v3.
There's still a regression, but it's about halved compared to v2, and
about equal to v1. So I tried doing the same tweak for v1, but that
didn't help much (if at all).
The results seem fairly stable, and the overall trend is clear. It'd be
great if there were no regressions, but considering how narrow is this
microbenchmark (and considering the benefits for practical COPY runs),
I'd say it's probably OK.
regards
--
Tomas Vondra
Attachment
pgsql-hackers by date: