Skip site navigation (1) Skip section navigation (2)

Unnecessary scan on a partial index slows down query dramatically

From: "Jimmy Choi" <yhjchoi(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unnecessary scan on a partial index slows down query dramatically
Date: 2008-04-25 15:40:53
Message-ID: 5770602b0804250840h203162e9gbaed05051b68ce51@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-04-25 16:13:55
Subject: Re: Unnecessary scan on a partial index slows down query dramatically
Previous:From: Steve AtkinsDate: 2008-04-25 15:29:44
Subject: Re: How to modify ENUM datatypes?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group