Why is now()::date so much faster than current_date

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема Why is now()::date so much faster than current_date
Дата
Msg-id n2epmf$647$1@ger.gmane.org
обсуждение исходный текст
Ответы Re: Why is now()::date so much faster than current_date  (David Rowley <david.rowley@2ndquadrant.com>)
Список pgsql-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?


В списке pgsql-performance по дате отправления:

Предыдущее
От: Merlin Moncure
Дата:
Сообщение: Re: Recursive query performance issue
Следующее
От: David Rowley
Дата:
Сообщение: Re: Why is now()::date so much faster than current_date