Hi
ne 18. 2. 2024 v 19:50 odesÃlatel Erik Wienhold <e...@ewie.name> napsal:
> 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)
>
The overhead of parse_type_and_format can be related to higher planning
time. PL/pgSQL can assign composite without usage FROM clause.
Regards
Pavel
> --
> Erik
>
