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.
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??? |