Hello,
A simple query is executing much slower than expected. When looking at
the query plan, I see a bitmap index scan on a partial index that does
not have any associated index condition. How could that happen?
The query is:
select id from test_run_results where test_run_id = 12902 and status = 3
The query plan is:
"Bitmap Heap Scan on test_run_results (cost=3240.97..3963.72
rows=2556 width=250)"
" Recheck Cond: ((test_run_id = 12902) AND (status = 3))"
" -> BitmapAnd (cost=3240.97..3240.97 rows=243 width=0)"
" -> Bitmap Index Scan on trr_same_status_in_run_index
(cost=0.00..24.33 rows=2556 width=0)"
" Index Cond: ((test_run_id = 12902) AND (status = 3))"
" -> Bitmap Index Scan on
trr_same_failure_reason_for_owner_index (cost=0.00..3216.39
rows=884694 width=0)"
My question refers to the last bitmap index scan which does not have
an associated index cond line.
trr_same_status_in_run_index is defined on (test_run_id, status)
and
trr_same_failure_reason_for_owner_index is a partial index defined on
(owner_id, failure_reason) where status = 3.
"vacuum analyze" doesn't solve the problem. I'm running 8.1.5.
As you can see, there really isn't much reason to use the partial index at all.
Remove "status = 3" from the query gets rid of the useless index scan
and makes the query much faster.
Thank,
Jimmy
