Postgresql is failing to use my covering index and falling back to a much slower bitmap scan

I'm trying to figure out why a table of mine is using a Bitmap Heap Scan when a index scan is dramatically faster.

Table:

```
webarchive=# \d web_pages
Table "public.web_pages"
Column | Type | Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
id | bigint | not null default nextval('web_pages_id_seq'::regclass)
state | dlstate_enum | not null
errno | integer |
url | text | not null
starturl | text | not null
netloc | text | not null
file | bigint |
priority | integer | not null
distance | integer | not null
is_text | boolean |
limit_netloc | boolean |
title | citext |
mimetype | text |
type | itemtype_enum |
content | text |
fetchtime | timestamp without time zone |
addtime | timestamp without time zone |
normal_fetch_mode | boolean | default true
ignoreuntiltime | timestamp without time zone | not null default '1970-01-01 00:00:00'::timestamp without time zone
Indexes:
"web_pages_pkey" PRIMARY KEY, btree (id)
"ix_web_pages_url" UNIQUE, btree (url)
"ix_web_pages_distance" btree (distance)
"ix_web_pages_fetchtime" btree (fetchtime)
"ix_web_pages_id" btree (id)
"ix_web_pages_id_state" btree (id, state)
"ix_web_pages_netloc" btree (netloc)
"ix_web_pages_priority" btree (priority)
"ix_web_pages_state" btree (state)
"web_pag

This part is clearly deranged:

->  Bitmap Index Scan on ix_web_pages_state  
     (cost=0.00..147.41 rows=6779 width=0) 
     (actual time=2021.616..2021.617 rows=11668131 loops=1)
Index Cond: (state = 'fetching'::dlstate_enum)

It is finding over a thousand times more rows than it thinks. A VACUUM ANALYZE should have fixed this. Perhaps your vacuum didn't do much because some long-open transaction is preventing it from removing dead tuples?

Doing VACUUM VERBOSE ANALYZE could shed some light on that.

You can also explore this further by using the test query:

EXPLAIN (ANALYZE,BUFFERS) SELECT COUNT(*) FROM web_pages WHERE state = 'fetching'

Try to get it to run as both a bitmap scan, and regular index scan by fiddling with enable_* parameters, so we can compare the two reports. In particular, a held-open transaction will lead to the actual count for the bitmap index scan node being higher, as it must count rows before testing for visibility, while the ordinary index scan will discard invisible rows before increment the count.

StackExchange Database Administrators Q#221728, answer score: 5