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

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> 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.

OK ... I prefer to do this sort of timing in a way that's not so
dependent on client I/O speeds, along the lines of

select count(date_part('day', current_date + g * interval '1 day')) from generate_series(0, :N) g;

I applied the v5 patch and ran your test suite that way, producing
the attached results. It looks pretty promising for me, too.
Most of the cases show about 10%-15% degradation:

# select extract, date_part, extract/date_part as ratio, unit from (select sum(msec) filter (where fn = 'extract') as extract, sum(msec) filter (where fn = 'date_part') as date_part, unit from timings group by unit) ss order by ratio;
extract | date_part | ratio | unit
-----------+-----------+------------------------+-----------------
22690.100 | 20705.402 | 1.09585411575201486066 | decade
22810.005 | 20754.296 | 1.09904980636298142804 | century
11238.122 | 10190.385 | 1.10281623314526389337 | timezone_minute
20201.992 | 18303.982 | 1.1036938301184955 | doy
20121.073 | 18206.290 | 1.1051715094069138 | dow
23209.090 | 20915.715 | 1.10964841507928368693 | millennium
18839.455 | 16943.063 | 1.11192734159106886399 | week
20130.843 | 18010.011 | 1.1177585066438882 | isoyear
19755.296 | 17668.497 | 1.11810846163089027890 | isodow
22500.373 | 20112.264 | 1.11873894455641592612 | day
22631.485 | 20200.266 | 1.12035579135443067928 | month
22883.344 | 20407.733 | 1.12130749652594925659 | quarter
22628.524 | 20172.361 | 1.12175882634660365239 | year
26503.545 | 23493.288 | 1.12813263941598979249 | minute
26381.817 | 23329.924 | 1.13081452815705700542 | hour
27236.886 | 24070.860 | 1.13152940941869131390 | microseconds
11563.820 | 9948.148 | 1.1624093248311143 | timezone_hour
27728.212 | 23567.973 | 1.17652086583771968849 | second
28348.328 | 23984.219 | 1.18195751965073367617 | milliseconds
49902.129 | 30798.034 | 1.6203024193037776 | epoch
31544.035 | 18250.745 | 1.7283697186060076 | julian
(21 rows)

The outliers are epoch and julian, which unsurprisingly are the
ones you didn't fix yet.

I think a ten-percent-ish slowdown is acceptable for this purpose,
so I think if you can address the points already raised then we're
pretty much good to go with this.

regards, tom lane