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
" 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
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)
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.
pgsql-general by date
|Next:||From: Tom Lane||Date: 2008-04-25 16:13:55|
|Subject: Re: Unnecessary scan on a partial index slows down query dramatically |
|Previous:||From: Steve Atkins||Date: 2008-04-25 15:29:44|
|Subject: Re: How to modify ENUM datatypes?|