Thread: Unnecessary scan on a partial index slows down query dramatically
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
"Jimmy Choi" <yhjchoi@gmail.com> writes: > 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? Easily --- it thinks that the partial index predicate is useful in itself. > "vacuum analyze" doesn't solve the problem. I'm running 8.1.5. choose_bitmap_and was rewritten (again) in 8.1.9. Try a newer version. http://archives.postgresql.org/pgsql-committers/2007-04/msg00233.php regards, tom lane
Thanks. I'm not really in a position to upgrade at the moment. I guess in the short-term I'll tweak the query to work around this (e.g. removing "status = 3" or adding more refining conditions both seem to work). Please let me know if there are configuration settings I should try. Thanks, Jimmy On Fri, Apr 25, 2008 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Jimmy Choi" <yhjchoi@gmail.com> writes: > > 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? > > Easily --- it thinks that the partial index predicate is useful in > itself. > > > > "vacuum analyze" doesn't solve the problem. I'm running 8.1.5. > > choose_bitmap_and was rewritten (again) in 8.1.9. Try a newer > version. > > http://archives.postgresql.org/pgsql-committers/2007-04/msg00233.php > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Fri, Apr 25, 2008 at 12:29:48PM -0400, Jimmy Choi wrote: > Thanks. I'm not really in a position to upgrade at the moment. You know that an upgrade of 8.1 to the latest is not a dump and restore, and that running an older stability-and-security release of the software is probably more dangerous than the upgrade itself, right? (What you're doing is akin to refusing to upgrade your production operating system to the latest patchlevel.) A
"Jimmy Choi" <yhjchoi@gmail.com> writes:
> Thanks. I'm not really in a position to upgrade at the moment.
Why not? A minor version update doesn't take much more than stop the
server, install new binaries, start the server.
regards, tom lane