On 17 November 2015 at 21:49, Thomas Kellerer <spam_ea...@gmx.net> wrote:
> 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 > > > The key to this is in the EXPLAIN VERBOSE output: postgres=# explain verbose select current_date; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) Output: ('now'::cstring)::date (2 rows) You can see that the implementation of current_date requires using the date_in() function as well as the date_out() function. date_in() parses the 'now' string, then the resulting date is converted back into a date string with date_out(). Using now()::date does not have to parse any date strings, it just needs to call date_out() to give the final output. The reason for this is likely best explained by the comment in gram.y: /* * Translate as "'now'::text::date". * * We cannot use "'now'::date" because coerce_type() will * immediately reduce that to a constant representing * today's date. We need to delay the conversion until * runtime, else the wrong things will happen when * CURRENT_DATE is used in a column default value or rule. * * This could be simplified if we had a way to generate * an expression tree representing runtime application * of type-input conversion functions. (As of PG 7.3 * that is actually possible, but not clear that we want * to rely on it.) * * The token location is attached to the run-time * typecast, not to the Const, for the convenience of * pg_stat_statements (which doesn't want these constructs * to appear to be replaceable constants). */ -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services
