Re: Patch: Add parse_type Function - Mailing list pgsql-hackers

On 2024-02-12 19:20 +0100, Tom Lane wrote:
> I wrote:
> > It strikes me that this is basically to_regtype() with the additional
> > option to return the typmod.  That leads to some questions:
> 
> BTW, another way that this problem could be approached is to use
> to_regtype() as-is, with a separate function to obtain the typmod:
> 
> select format_type(to_regtype('timestamp(4)'), to_regtypmod('timestamp(4)'));
> 
> This is intellectually ugly, since it implies parsing the same
> typename string twice.  But on the other hand it avoids the notational
> pain and runtime overhead involved in using a record-returning
> function.  So I think it might be roughly a wash for performance.
> Question to think about is which way is easier to use.  I don't
> have an opinion particularly; just throwing the idea out there.

Out of curiosity, I benchmarked this with the attached to_regtypmod()
patch based on David's v5 applied to a6c21887a9.  The script running
pgbench and its output are included at the end.

Just calling parse_type() vs to_regtype()/to_regtypmod() is a wash for
performance as you thought.  But format_type() performs better with
to_regtypmod() than with parse_type().  Accessing the record fields
returned by parse_type() adds some overhead.

to_regtypmod() is better for our use case in pgTAP which relies on
format_type() to normalize the type name.  The implementation of
to_regtypmod() is also simpler than parse_type().  Usage-wise, both are
clunky IMO.

Benchmark script:

    #!/usr/bin/env bash
    
    set -eu
    
    cat <<'SQL' > parse_type.sql
    SELECT parse_type('interval second(0)');
    SQL
    
    cat <<'SQL' > parse_type_and_format.sql
    SELECT format_type(p.typid, p.typmod) FROM parse_type('interval second(0)') p;
    SQL
    
    cat <<'SQL' > to_regtypmod.sql
    SELECT to_regtype('interval second(0)'), to_regtypmod('interval second(0)');
    SQL
    
    cat <<'SQL' > to_regtypmod_and_format.sql
    SELECT format_type(to_regtype('interval second(0)'), to_regtypmod('interval second(0)'));
    SQL
    
    for f in \
        parse_type.sql \
        parse_type_and_format.sql \
        to_regtypmod.sql \
        to_regtypmod_and_format.sql
    do
        pgbench -n -f "$f" -T10 postgres
        echo
    done

pgbench output:

    pgbench (17devel)
    transaction type: parse_type.sql
    scaling factor: 1
    query mode: simple
    number of clients: 1
    number of threads: 1
    maximum number of tries: 1
    duration: 10 s
    number of transactions actually processed: 277017
    number of failed transactions: 0 (0.000%)
    latency average = 0.036 ms
    initial connection time = 1.623 ms
    tps = 27706.005513 (without initial connection time)
    
    pgbench (17devel)
    transaction type: parse_type_and_format.sql
    scaling factor: 1
    query mode: simple
    number of clients: 1
    number of threads: 1
    maximum number of tries: 1
    duration: 10 s
    number of transactions actually processed: 222487
    number of failed transactions: 0 (0.000%)
    latency average = 0.045 ms
    initial connection time = 1.603 ms
    tps = 22252.095670 (without initial connection time)
    
    pgbench (17devel)
    transaction type: to_regtypmod.sql
    scaling factor: 1
    query mode: simple
    number of clients: 1
    number of threads: 1
    maximum number of tries: 1
    duration: 10 s
    number of transactions actually processed: 276134
    number of failed transactions: 0 (0.000%)
    latency average = 0.036 ms
    initial connection time = 1.570 ms
    tps = 27617.628259 (without initial connection time)
    
    pgbench (17devel)
    transaction type: to_regtypmod_and_format.sql
    scaling factor: 1
    query mode: simple
    number of clients: 1
    number of threads: 1
    maximum number of tries: 1
    duration: 10 s
    number of transactions actually processed: 270820
    number of failed transactions: 0 (0.000%)
    latency average = 0.037 ms
    initial connection time = 1.631 ms
    tps = 27086.331104 (without initial connection time)

-- 
Erik