query planner oddity for data constant vs current_date
От | Bryan White |
---|---|
Тема | query planner oddity for data constant vs current_date |
Дата | |
Msg-id | 005b01c24549$730b6150$2000a8c0@bryan обсуждение исходный текст |
Ответы |
Re: query planner oddity for data constant vs current_date
|
Список | pgsql-general |
I have a table with an index on 'shipdate'. There is about 1.5Million records in this table with on the order of 1000 to 2000 for any given date. The table is vacuum analysed nightly. I have noted that selects base on a date literal are perform differently than selects based on the CURRENT_DATE. For example: ------ explain select count(*) from orders where shipdate = current_date; Aggregate (cost=98881.63..98881.63 rows=1 width=0) -> Seq Scan on orders (cost=0.00..98877.02 rows=1843 width=0) explain select count(*) from orders where shipdate = '8/16/2002'; Aggregate (cost=1760.87..1760.87 rows=1 width=0) -> Index Scan using iordshipdate on orders (cost=0.00..1756.44 rows=1770 width=0) ------ In the first case the qquery will take about 30 seconds to run. In the second case the query takes less than a second. --------- Bryan White This email represents the consensus opinion of the many voices in my head.
В списке pgsql-general по дате отправления: