index scan vs bitmap index scan
От | Wayne Cuddy |
---|---|
Тема | index scan vs bitmap index scan |
Дата | |
Msg-id | 20130206011349.GA18317@slacker.ja10629.home обсуждение исходный текст |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: