Re: remaining sql/json patches - Mailing list pgsql-hackers

From Andrew Dunstan
Subject Re: remaining sql/json patches
Date
Msg-id 21a7374f-c63f-7934-0223-8da6cb681aa0@dunslane.net
Whole thread Raw
In response to Re: remaining sql/json patches  (Andrew Dunstan <andrew@dunslane.net>)
Responses Re: remaining sql/json patches
List pgsql-hackers
On 2023-11-28 Tu 15:49, Andrew Dunstan wrote:
>
> On 2023-11-28 Tu 00:10, John Naylor wrote:
>> On Mon, Nov 27, 2023 at 8:57 PM Andrew Dunstan <andrew@dunslane.net> 
>> wrote:
>>> Interesting. But inferring a speed effect from such changes is
>>> difficult. I don't have a good idea about measuring parser speed, but a
>>> tool to do that would be useful. Amit has made a start on such
>>> measurements, but it's only a start. I'd prefer to have evidence rather
>>> than speculation.
>> Tom shared this test a while back, and that's the one I've used in the
>> past. The downside for a micro-benchmark like that is that it can
>> monopolize the CPU cache. Cache misses in real world queries are
>> likely much more dominant.
>>
>> https://www.postgresql.org/message-id/14616.1558560331@sss.pgh.pa.us
>
>
>
> Cool, I took this and ran with it a bit. (See attached) Here are 
> comparative timings for 1000 iterations parsing most of the 
> information_schema.sql, all the way back to 9.3:
>
>
> ==== REL9_3_STABLE ====
> Time: 3998.701 ms
> ==== REL9_4_STABLE ====
> Time: 3987.596 ms
> ==== REL9_5_STABLE ====
> Time: 4129.049 ms
> ==== REL9_6_STABLE ====
> Time: 4145.777 ms
> ==== REL_10_STABLE ====
> Time: 4140.927 ms (00:04.141)
> ==== REL_11_STABLE ====
> Time: 4145.078 ms (00:04.145)
> ==== REL_12_STABLE ====
> Time: 3528.625 ms (00:03.529)
> ==== REL_13_STABLE ====
> Time: 3356.067 ms (00:03.356)
> ==== REL_14_STABLE ====
> Time: 3401.406 ms (00:03.401)
> ==== REL_15_STABLE ====
> Time: 3372.491 ms (00:03.372)
> ==== REL_16_STABLE ====
> Time: 1654.056 ms (00:01.654)
> ==== HEAD ====
> Time: 1614.949 ms (00:01.615)
>
>
> This is fairly repeatable.
>
> The first good news is that the parser is pretty fast. Even 4ms to 
> parse almost all the information schema setup is pretty good.
>
> The second piece of good news is that recent modifications have vastly 
> improved the speed. So even if the changes from the SQL/JSON patches 
> eat up a bit of that gain, I think we're in good shape.
>
> In a few days I'll re-run the test with the SQL/JSON patches applied.
>
>

To avoid upsetting the cfbot, I published the code here: 
<https://github.com/adunstan/parser_benchmark>


cheers


andrew



Andrew Dunstan
EDB: https://www.enterprisedb.com




pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: remaining sql/json patches
Next
From: Tomas Vondra
Date:
Subject: Re: logical decoding and replication of sequences, take 2