Horton

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

Peter Eisentraut <peter.eisentraut@2ndquadrant.com>

View thread View on postgr.es
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Steele <david@pgmasters.net>, Pavel Stehule <pavel.stehule@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-03-22T20:58:20Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

On 19.03.21 21:06, Tom Lane wrote:
> I guess the immediate question is how much of a performance gap there
> is now between the float and numeric implementations.

Attached are my test script and the full output.

To summarize, for cases that don't do any interesting computation and 
where the overhead is only the data type passing, the difference is like 
this:

-- old
select date_part('microseconds', current_timestamp + generate_series(0, 
10000000) * interval '1 second') \g /dev/null
Time: 2760.966 ms (00:02.761)

-- new
select extract(microseconds from current_timestamp + generate_series(0, 
10000000) * interval '1 second') \g /dev/null
Time: 3178.477 ms (00:03.178)

Commits

  1. Fix inconsistent equalfuncs.c behavior for FuncCall.funcformat.

  2. Doc: fix discussion of how to get real Julian Dates.

  3. Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation.

  4. Change return type of EXTRACT to numeric

  5. Improve our ability to regurgitate SQL-syntax function calls.

  6. Add more tests for EXTRACT of date type

  7. Expose internal function for converting int64 to numeric

  8. Change floating-point output format for improved performance.