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)
|
Список | 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 по дате отправления: