pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Steele <david@pgmasters.net>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Petr Fedorov <petr.fedorov@phystech.edu>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Date: Mon, 22 Mar 2021 21:58:20 +0100
Message-ID: <fcb54768-3d0a-d742-4bdb-4f2cef43d094@2ndquadrant.com> (raw)
In-Reply-To: <4158031.1616184417@sss.pgh.pa.us>
References: <CA+hUKGK+K8NyS57tU841m1tSF6ha3W0udN2dw_Hbc=D_0OPbmA@mail.gmail.com>
	<ee683994-8f7c-aded-be43-543e66e68b94@2ndquadrant.com>
	<a3be61d9-f44b-7fce-3dc8-d700fdfb6f48@2ndquadrant.com>
	<CAFj8pRC8cgoiQMT235cky4Pa40gj=s0m3PftmaJLSt9MAiwj_Q@mail.gmail.com>
	<be30c07c-3f7b-7c86-f1c5-d0858046b26f@2ndquadrant.com>
	<1551357.1599435992@sss.pgh.pa.us>
	<097a4242-0748-98e3-06ab-ded1d366cd76@2ndquadrant.com>
	<1893468.1599658711@sss.pgh.pa.us>
	<38fc499a-ae52-efb6-aad6-82f812d6782d@2ndquadrant.com>
	<20200917055327.GU2873@paquier.xyz>
	<20200930071543.GL1996@paquier.xyz>
	<2fb99632-9302-6509-78ce-3aaed3fd7f40@2ndquadrant.com>
	<3ff4be02-1e2b-564f-9b53-3dc561da8218@pgmasters.net>
	<3013402.1615829712@sss.pgh.pa.us>
	<8fae8673-0d5b-12d9-7342-daed0d590594@2ndquadrant.com>
	<901bcfb8-36c8-27b2-b148-042392992eeb@2ndquadrant.com>
	<4158031.1616184417@sss.pgh.pa.us>


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)

Attachments:

output.txt (text/plain, 21.7K) download
-- date
select date_part('day', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2753.082 ms (00:02.753)
select date_part('month', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2777.257 ms (00:02.777)
select date_part('quarter', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2788.313 ms (00:02.788)
select date_part('week', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2842.797 ms (00:02.843)
select date_part('year', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2848.463 ms (00:02.848)
select date_part('decade', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2844.086 ms (00:02.844)
select date_part('century', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2824.671 ms (00:02.825)
select date_part('millennium', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 2846.615 ms (00:02.847)
select date_part('julian', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3011.378 ms (00:03.011)
select date_part('isoyear', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3059.134 ms (00:03.059)
select date_part('dow', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3014.891 ms (00:03.015)
select date_part('isodow', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3054.004 ms (00:03.054)
select date_part('doy', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3101.087 ms (00:03.101)
select date_part('epoch', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3156.491 ms (00:03.156)
select extract(day from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3303.320 ms (00:03.303)
select extract(month from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3289.939 ms (00:03.290)
select extract(quarter from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3301.930 ms (00:03.302)
select extract(week from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3347.644 ms (00:03.348)
select extract(year from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3358.538 ms (00:03.359)
select extract(decade from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3351.724 ms (00:03.352)
select extract(century from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3282.368 ms (00:03.282)
select extract(millennium from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3358.242 ms (00:03.358)
select extract(julian from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 5758.773 ms (00:05.759) FIXME
select extract(isoyear from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3780.507 ms (00:03.781)
select extract(dow from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3688.659 ms (00:03.689)
select extract(isodow from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3716.656 ms (00:03.717)
select extract(doy from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 3636.682 ms (00:03.637)
select extract(epoch from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
Time: 6423.341 ms (00:06.423) FIXME
-- time
select date_part('microseconds', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2425.741 ms (00:02.426)
select date_part('milliseconds', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2790.781 ms (00:02.791)
select date_part('second', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2900.706 ms (00:02.901)
select date_part('minute', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2337.388 ms (00:02.337)
select date_part('hour', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2318.059 ms (00:02.318)
select date_part('epoch', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2747.020 ms (00:02.747)
select extract(microseconds from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2962.310 ms (00:02.962)
select extract(milliseconds from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3153.851 ms (00:03.154)
select extract(second from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3210.870 ms (00:03.211)
select extract(minute from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3124.923 ms (00:03.125)
select extract(hour from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3075.109 ms (00:03.075)
select extract(epoch from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3559.993 ms (00:03.560)
-- timetz
select date_part('microseconds', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2614.399 ms (00:02.614)
select date_part('milliseconds', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3111.845 ms (00:03.112)
select date_part('second', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2959.470 ms (00:02.959)
select date_part('minute', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2516.685 ms (00:02.517)
select date_part('hour', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2516.936 ms (00:02.517)
select date_part('timezone_minute', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2523.532 ms (00:02.524)
select date_part('timezone_hour', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2602.080 ms (00:02.602)
select date_part('epoch', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2938.228 ms (00:02.938)
select extract(microseconds from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3275.301 ms (00:03.275)
select extract(milliseconds from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3407.035 ms (00:03.407)
select extract(second from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3309.767 ms (00:03.310)
select extract(minute from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3025.000 ms (00:03.025)
select extract(hour from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2991.279 ms (00:02.991)
select extract(timezone_minute from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2966.017 ms (00:02.966)
select extract(timezone_hour from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3357.941 ms (00:03.358)
select extract(epoch from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3541.374 ms (00:03.541)
-- timestamp
select date_part('microseconds', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3152.124 ms (00:03.152)
select date_part('milliseconds', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3565.697 ms (00:03.566)
select date_part('second', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3263.849 ms (00:03.264)
select date_part('minute', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2905.160 ms (00:02.905)
select date_part('hour', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2851.960 ms (00:02.852)
select date_part('day', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2862.492 ms (00:02.862)
select date_part('month', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2916.239 ms (00:02.916)
select date_part('quarter', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2910.540 ms (00:02.911)
select date_part('week', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2983.335 ms (00:02.983)
select date_part('year', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2897.387 ms (00:02.897)
select date_part('decade', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2905.314 ms (00:02.905)
select date_part('century', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2912.690 ms (00:02.913)
select date_part('millennium', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3002.772 ms (00:03.003)
select date_part('julian', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3352.501 ms (00:03.353)
select date_part('isoyear', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3207.046 ms (00:03.207)
select date_part('dow', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3140.982 ms (00:03.141)
select date_part('isodow', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3118.173 ms (00:03.118)
select date_part('doy', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3152.199 ms (00:03.152)
select date_part('epoch', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3415.433 ms (00:03.415)
select extract(microseconds from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3528.937 ms (00:03.529)
select extract(milliseconds from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3729.558 ms (00:03.730)
select extract(second from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3679.884 ms (00:03.680)
select extract(minute from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3349.723 ms (00:03.350)
select extract(hour from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3370.358 ms (00:03.370)
select extract(day from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3434.585 ms (00:03.435)
select extract(month from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3557.226 ms (00:03.557)
select extract(quarter from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3426.338 ms (00:03.426)
select extract(week from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3461.903 ms (00:03.462)
select extract(year from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3411.337 ms (00:03.411)
select extract(decade from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3556.944 ms (00:03.557)
select extract(century from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3368.663 ms (00:03.369)
select extract(millennium from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3426.442 ms (00:03.426)
select extract(julian from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 7066.600 ms (00:07.067) FIXME
select extract(isoyear from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3718.849 ms (00:03.719)
select extract(dow from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3714.132 ms (00:03.714)
select extract(isodow from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3671.153 ms (00:03.671)
select extract(doy from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3759.612 ms (00:03.760)
select extract(epoch from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 6182.779 ms (00:06.183) FIXME
-- timestamptz
select date_part('microseconds', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2760.966 ms (00:02.761)
select date_part('milliseconds', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3086.635 ms (00:03.087)
select date_part('second', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2999.753 ms (00:03.000)
select date_part('minute', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2661.133 ms (00:02.661)
select date_part('hour', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2657.962 ms (00:02.658)
select date_part('day', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2688.195 ms (00:02.688)
select date_part('month', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2669.395 ms (00:02.669)
select date_part('quarter', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2685.404 ms (00:02.685)
select date_part('week', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2758.582 ms (00:02.759)
select date_part('year', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2724.754 ms (00:02.725)
select date_part('decade', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2960.629 ms (00:02.961)
select date_part('century', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2783.304 ms (00:02.783)
select date_part('millennium', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2763.550 ms (00:02.764)
select date_part('julian', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3183.417 ms (00:03.183)
select date_part('isoyear', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3012.545 ms (00:03.013)
select date_part('dow', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3094.168 ms (00:03.094)
select date_part('isodow', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3062.064 ms (00:03.062)
select date_part('doy', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2979.621 ms (00:02.980)
select date_part('timezone_minute', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2542.700 ms (00:02.543)
select date_part('timezone_hour', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2647.099 ms (00:02.647)
select date_part('epoch', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2735.699 ms (00:02.736)
select extract(microseconds from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3178.477 ms (00:03.178)
select extract(milliseconds from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3331.381 ms (00:03.331)
select extract(second from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3280.779 ms (00:03.281)
select extract(minute from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3080.931 ms (00:03.081)
select extract(hour from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3252.902 ms (00:03.253)
select extract(day from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2963.922 ms (00:02.964)
select extract(month from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3102.402 ms (00:03.102)
select extract(quarter from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3030.562 ms (00:03.031)
select extract(week from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3094.769 ms (00:03.095)
select extract(year from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3037.351 ms (00:03.037)
select extract(decade from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3039.483 ms (00:03.039)
select extract(century from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3020.867 ms (00:03.021)
select extract(millennium from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3082.613 ms (00:03.083)
select extract(julian from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 6624.328 ms (00:06.624) FIXME
select extract(isoyear from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3388.350 ms (00:03.388)
select extract(dow from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3370.562 ms (00:03.371)
select extract(isodow from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3359.692 ms (00:03.360)
select extract(doy from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3389.745 ms (00:03.390)
select extract(timezone_minute from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3036.885 ms (00:03.037)
select extract(timezone_hour from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 3089.325 ms (00:03.089)
select extract(epoch from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 5396.158 ms (00:05.396) FIXME
-- interval
select date_part('microseconds', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2112.757 ms (00:02.113)
select date_part('milliseconds', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2204.998 ms (00:02.205)
select date_part('second', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2058.778 ms (00:02.059)
select date_part('minute', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2070.273 ms (00:02.070)
select date_part('hour', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2070.876 ms (00:02.071)
select date_part('day', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 1970.228 ms (00:01.970)
select date_part('month', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 1970.330 ms (00:01.970)
select date_part('quarter', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2051.003 ms (00:02.051)
select date_part('year', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 1920.614 ms (00:01.921)
select date_part('decade', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 1961.488 ms (00:01.961)
select date_part('century', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 1994.821 ms (00:01.995)
select date_part('millennium', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2054.538 ms (00:02.055)
select date_part('epoch', generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2212.801 ms (00:02.213)
select extract(microseconds from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2691.245 ms (00:02.691)
select extract(milliseconds from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2794.298 ms (00:02.794)
select extract(second from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2697.065 ms (00:02.697)
select extract(minute from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2488.450 ms (00:02.488)
select extract(hour from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2540.256 ms (00:02.540)
select extract(day from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2391.730 ms (00:02.392)
select extract(month from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2397.520 ms (00:02.398)
select extract(quarter from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2521.661 ms (00:02.522)
select extract(year from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2446.734 ms (00:02.447)
select extract(decade from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2466.988 ms (00:02.467)
select extract(century from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2416.530 ms (00:02.417)
select extract(millennium from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 2440.792 ms (00:02.441)
select extract(epoch from generate_series(0, 10000000) * interval '1 second') \g /dev/null
Time: 5784.732 ms (00:05.785) FIXME

test.sql (text/plain, 16.9K) download
\set ECHO all
\timing on

-- date

select date_part('day', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('month', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('quarter', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('week', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('year', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('decade', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('century', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('millennium', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('julian', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('isoyear', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('dow', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('isodow', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('doy', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select date_part('epoch', current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null

select extract(day from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(month from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(quarter from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(week from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(year from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(decade from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(century from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(millennium from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(julian from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(isoyear from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(dow from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(isodow from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(doy from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null
select extract(epoch from current_date + generate_series(0, 10000000) * interval '1 day') \g /dev/null

-- time

select date_part('microseconds', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('milliseconds', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('second', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('minute', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('hour', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('epoch', localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null

select extract(microseconds from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(milliseconds from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(second from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(minute from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(hour from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(epoch from localtime + generate_series(0, 10000000) * interval '1 second') \g /dev/null

-- timetz

select date_part('microseconds', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('milliseconds', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('second', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('minute', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('hour', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('timezone_minute', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('timezone_hour', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('epoch', current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null

select extract(microseconds from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(milliseconds from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(second from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(minute from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(hour from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(timezone_minute from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(timezone_hour from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(epoch from current_time + generate_series(0, 10000000) * interval '1 second') \g /dev/null

-- timestamp

select date_part('microseconds', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('milliseconds', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('second', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('minute', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('hour', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('day', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('month', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('quarter', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('week', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('year', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('decade', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('century', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('millennium', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('julian', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('isoyear', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('dow', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('isodow', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('doy', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('epoch', localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null

select extract(microseconds from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(milliseconds from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(second from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(minute from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(hour from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(day from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(month from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(quarter from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(week from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(year from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(decade from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(century from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(millennium from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(julian from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(isoyear from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(dow from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(isodow from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(doy from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(epoch from localtimestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null

-- timestamptz

select date_part('microseconds', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('milliseconds', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('second', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('minute', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('hour', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('day', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('month', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('quarter', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('week', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('year', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('decade', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('century', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('millennium', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('julian', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('isoyear', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('dow', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('isodow', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('doy', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('timezone_minute', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('timezone_hour', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('epoch', current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null

select extract(microseconds from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(milliseconds from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(second from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(minute from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(hour from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(day from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(month from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(quarter from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(week from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(year from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(decade from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(century from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(millennium from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(julian from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(isoyear from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(dow from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(isodow from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(doy from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(timezone_minute from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(timezone_hour from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(epoch from current_timestamp + generate_series(0, 10000000) * interval '1 second') \g /dev/null

-- interval

select date_part('microseconds', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('milliseconds', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('second', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('minute', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('hour', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('day', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('month', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('quarter', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('year', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('decade', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('century', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('millennium', generate_series(0, 10000000) * interval '1 second') \g /dev/null
select date_part('epoch', generate_series(0, 10000000) * interval '1 second') \g /dev/null

select extract(microseconds from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(milliseconds from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(second from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(minute from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(hour from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(day from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(month from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(quarter from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(year from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(decade from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(century from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(millennium from generate_series(0, 10000000) * interval '1 second') \g /dev/null
select extract(epoch from generate_series(0, 10000000) * interval '1 second') \g /dev/null

view thread (47+ messages)  latest in thread

Message-ID: <fcb54768-3d0a-d742-4bdb-4f2cef43d094@2ndquadrant.com>
Permalink:  ../fcb54768-3d0a-d742-4bdb-4f2cef43d094@2ndquadrant.com/
Also on:    postgresql.org/message-id/fcb54768-3d0a-d742-4bdb-4f2cef43d094@2ndquadrant.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: peter.eisentraut@2ndquadrant.com, tgl@sss.pgh.pa.us, david@pgmasters.net, pavel.stehule@gmail.com, thomas.munro@gmail.com, petr.fedorov@phystech.edu, pgsql-hackers@postgresql.org
  Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
  In-Reply-To: <fcb54768-3d0a-d742-4bdb-4f2cef43d094@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox