I have a table with with an index that is of type 'timestamp without
time zone'. Multiple records are inserted per second so the index is not
unique.
This table experiences frequent inserts and updates. Bulk deletes are
performed once per month.
Slower than expected search times are experienced when performing
queries that limit the result to a range of dates. It's always a simple
range: 'where ts between A and B'.
I copied the table to another system where no inserts/updates are taking
place and the results are rendered much faster. I attribute some of this
to the I/O load on the idle system compared to our production system.
EXPLAIN shows that the difference is that on the idle system a
'bitmap index scan' is used. On the production system a 'index scan' is
used. On the production system query times are greatly reduced when 'set
enable_indexscans to off' is used.
Both backends are PGSQL 9.0.4.
The table has about 24 million records.
What influences the use of a bitmap index scan vs index scan?
Any pointers on what I can do to render faster performance without
having to explicitly adjust enable_indexscan would be greatly
appreciated.
Thanks,
Wayne