From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Why is now()::date so much faster than current_date
Date: Tue, 17 Nov 2015 09:49:18 +0100
Message-ID: <n2epmf$647$1@ger.gmane.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hello,
I stumbled over this answer: http://stackoverflow.com/a/9717125/330315 and this sounded quite strange to me.
So I ran this on my Windows laptop with Postgres 9.4.5, 64bit and indeed now()::date is much faster than current_date:
explain analyze
select current_date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..6.00 rows=1000 width=0) (actual time=243.878..1451.839 rows=1000000 loops=1)
Planning time: 0.047 ms
Execution time: 1517.881 ms
And:
explain analyze
select now()::date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..6.00 rows=1000 width=0) (actual time=244.491..785.819 rows=1000000 loops=1)
Planning time: 0.037 ms
Execution time: 826.612 ms
Running this on a CentOS 6.6. test server (Postgres 9.4.1, 64bit), there is still a difference, but not as big as on Windows:
explain analyze
select current_date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..15.00 rows=1000 width=0) (actual time=233.599..793.032 rows=1000000 loops=1)
Planning time: 0.087 ms
Execution time: 850.198 ms
And
explain analyze
select now()::date
from generate_series (1, 1000000);
Function Scan on generate_series (cost=0.00..15.00 rows=1000 width=0) (actual time=198.385..570.171 rows=1000000 loops=1)
Planning time: 0.074 ms
Execution time: 623.211 ms
Any ideas?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Why is now()::date so much faster than current_date
In-Reply-To: <n2epmf$647$1@ger.gmane.org>
* 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