Should the optimizer optimize "current_date - interval '1 days'" (fwd)

Поиск
Список
Период
Сортировка
От Ron Mayer
Тема Should the optimizer optimize "current_date - interval '1 days'" (fwd)
Дата
Msg-id Pine.LNX.4.33.0206031236310.12984-100000@ron
обсуждение исходный текст
Ответы Re: Should the optimizer optimize "current_date - interval '1 days'" (fwd)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
On a very big table (a data warehouse with >10 million rows), I frequently
run queries looking at the past few days.

However queries like this:
   select count(*)
     from fact
    where dat > (current_date - interval '1 days');
never uses the index I have on "fact".  (Thanks to previous queries it's now
ordered by 'dat' so the correlation in pg_stats is '1'.).

However if I toss on an extra where clause with a constant like
   select count(*)
     from fact
    where dat > (current_date - interval '1 days')
      and dat > '2002-05-20';
it hapily uses the index (at least for the second comparison).


Should it treat my current_dat... expression as a constant and use
the index?  Or is there a good reason it doesn't?

    Thanks,
    Ron


PS: This seems true even if I "set enable_seqscan to off".

logs2=# set enable_seqscan to off;
logs2=# explain
logs2-#   select count(*) from fact
logs2-#   where dat > (current_date - interval '1 days');
NOTICE:  QUERY PLAN:

Aggregate  (cost=101265332.77..101265332.77 rows=1 width=0)
  ->  Seq Scan on fact  (cost=100000000.00..101231544.46 rows=13515325 width=0)


logs2=# explain
logs2-#   select count(*)
logs2-#      from fact
logs2-#     where dat > (current_date - interval '1 days')
logs2-#       and dat > '2002-05-20';
NOTICE:  QUERY PLAN:

Aggregate  (cost=198729.54..198729.54 rows=1 width=0)
  ->  Index Scan using i__fact__dat on fact  (cost=0.00..194279.24
rows=1780119 width=0)

EXPLAIN
logs2=#

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

Предыдущее
От: Stephan Szabo
Дата:
Сообщение: Re: BUG: mismatched parentheses
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Should the optimizer optimize "current_date - interval '1 days'" (fwd)