Any way to favor index scans, but not bitmap index scans?

From: "Francisco Reyes" <lists(at)stringsutils(dot)com>
To: " Pgsql General list " <pgsql-general(at)postgresql(dot)org>
Subject: Any way to favor index scans, but not bitmap index scans?
Date: 2008-07-23 15:48:25
Message-ID: 83b9a071e2eef2b05b6d505521605ae6@stringsutils.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The data set I am working with has a very uneven distribution.

I had to to set random_page_cost = 0.75 to get good plans.
However, that first tries bitmap scans which perform very poorly.
Is there a way to have the planner to favor index scans and disfavor bitmap
scans? Is my best choice to just disable bitmap scans?

So far in this data set almost every time bitmap scans are used the queries
do worse, much worse. I had one extreme case where a sequential scan would
finish in 20 minutes and the same query using bitmap scans would take over
a day to finish.

My most recent test(before reducing random_page_cost)..
Sequential scan(default selected by planner): 12 minutes
Seq scan dissabled(bitmap index scans): 29.98 minutes
Seq scan disabled and bitmap index disabled: 3 seconds!

After I reduced random_page_cost and turned bitmap index off the planner is
picking up the right plan using an index scan. Now just testing more cases
to make sure the improvement is consistant for most of our queries.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shane Ambler 2008-07-23 15:56:29 Re: A couple of newbie questions ...
Previous Message Bill Wordsworth 2008-07-23 14:44:19 Re: pg_query transaction: auto rollback? begin or start?? commit or end???