Re: remaining sql/json patches

On 2023-11-28 Tu 00:10, John Naylor wrote:
> On Mon, Nov 27, 2023 at 8:57 PM Andrew Dunstan <andrew(at)dunslane(dot)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.

cheers

andrew

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