Why is the comparison between timestamp and date so much slower then between two dates

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема Why is the comparison between timestamp and date so much slower then between two dates
Дата
Msg-id neldbi$d40$1@ger.gmane.org
обсуждение исходный текст
Ответы Re: Why is the comparison between timestamp and date so much slower then between two dates
Список pgsql-general
I came across something strange today.

Consider the following table:

    CREATE TABLE price_history
    (
        product_id integer,
        valid_from date,
        valid_to date,
        price integer
    );

    CREATE INDEX i1 ON price_history (product_id, valid_from, valid_to);

The table contains 5 million rows and 5000 distinct product_ids
To get the current price for each product I used the following query:

   select *
   from price_history
   where current_date between valid_from and valid_to;

The execution plan is not really surprising:

    Index Scan using i1 on public.price_history  (cost=0.44..61980.61 rows=5133 width=16) (actual time=0.177..527.693
rows=5000loops=1) 
      Output: product_id, valid_from, price, valid_to
      Index Cond: ((('now'::cstring)::date >= ph.valid_from) AND (('now'::cstring)::date <= ph.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.395 ms
    Execution time: 528.193 ms

Now I tried the same query using now() instead of current_date, which shows exactly the same execution plan, but is 3
timesslower: 

    Index Scan using i1 on public.price_history  (cost=0.44..61980.60 rows=5133 width=16) (actual time=0.406..1902.241
rows=5000loops=1) 
      Output: product_id, valid_from, price, valid_to
      Index Cond: ((now() >= ph.valid_from) AND (now() <= ph.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.615 ms
    Execution time: 1902.777 ms

The above plans were taken on my Windows laptop with Postgres 9.5.1

On a CentOS server with 9.5.0 I can see the same difference:

Plan using now()

    Index Scan using i1 on public.price_history  (cost=0.44..110570.50 rows=44944 width=16) (actual time=0.182..837.903
rows=5000loops=1) 
      Output: product_id, valid_from, valid_to, price
      Index Cond: ((now() >= price_history.valid_from) AND (now() <= price_history.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.106 ms
    Execution time: 838.529 ms

Plan using current_date:

    Index Scan using i1 on public.price_history  (cost=0.44..110570.51 rows=44944 width=16) (actual time=0.052..180.856
rows=5000loops=1) 
      Output: product_id, valid_from, valid_to, price
      Index Cond: ((('now'::cstring)::date >= price_history.valid_from) AND (('now'::cstring)::date <=
price_history.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.115 ms
    Execution time: 181.226 ms

(I don't know why the estimates on the CentOS installation are so different from the one on my laptop given that both
tablescontain exactly the same data and were analyzed properly before running explain plan - but that is a different
question). 

So my question is: why is comparing a timestamp to a date so much slower?

Thomas



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

Предыдущее
От: Christoph Moench-Tegeder
Дата:
Сообщение: Re: Returning values from an array of JSONB objects.
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Why is the comparison between timestamp and date so much slower then between two dates