Re: Load TIME fields - proposed performance improvement - Mailing list pgsql-hackers

From Peter Smith
Subject Re: Load TIME fields - proposed performance improvement
Date
Msg-id CAHut+PtVc0MuE-F=sj0uFKGrUzNDyG_dy-ZxQkoyRUx6jY0+iQ@mail.gmail.com
Whole thread Raw
In response to Re: Load TIME fields - proposed performance improvement  (Peter Smith <smithpb2250@gmail.com>)
Responses Re: Load TIME fields - proposed performance improvement
List pgsql-hackers
The patch has been re-implemented based on previous advice.

Please see attached.

~

Test:

A test table was created and 20 million rows inserted as follows:

test=# create table t1 (id int, a timestamp, b time without time zone
default '01:02:03', c date default CURRENT_DATE, d time with time zone
default CURRENT_TIME, e time with time zone default LOCALTIME);
CREATE TABLE

$ time psql -d test -c "insert into t1(id, a)
values(generate_series(1,20000000), timestamp 'now');"

~

Observations:

BEFORE PATCH

perf results
6.18% GetSQLCurrentTime
5.73% GetSQLCurrentDate
5.20% GetSQLLocalTime
4.67% GetCurrentDateTime
-.--% GetCurrentTimeUsec

elapsed time
Run1 1m57s
Run2 1m58s
Run3 2m00s

AFTER PATCH

perf results
1.77% GetSQLCurrentTime
0.12% GetSQLCurrentDate
0.50% GetSQLLocalTime
0.36% GetCurrentDateTime
-.--% GetCurrentTimeUsec

elapsed time
Run1 1m36s
Run2 1m36s
Run3 1m36s

(represents 19% improvement for this worst case table/data)

~

Note: I patched the function GetCurrentTimeUsec consistently with the
others, but actually I was not able to discover any SQL syntax which
could cause that function to be invoked multiple times. Perhaps the
patch for that function should be removed?

---

Kind Regards,
Peter Smith
Fujitsu Australia

On Tue, Sep 22, 2020 at 1:06 PM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Tom.
>
> Thanks for your feedback.
>
> On Tue, Sep 22, 2020 at 12:44 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Still, for the size of the patch I'm envisioning, it'd be well
> > worth the trouble.
>
> The OP patch I gave was just a POC to test the effect and to see if
> the idea was judged as worthwhile...
>
> I will rewrite/fix it based on your suggestions.
>
> Kind Regards,
> Peter Smith.
> Fujitsu Australia.

Attachment

pgsql-hackers by date:

Previous
From: "Yang, Rong"
Date:
Subject: Problem of ko.po on branch REL9_5_STABLE
Next
From: Fujii Masao
Date:
Subject: Re: Feature improvement for FETCH tab completion