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.
