Is there any way to force the query optimizer to prefer one scan type
over another? I have a rather large query which the optimizer sometime
last night decided to switch from using index scans in a variety of
places to using sequential scans. This has resulted in a doubling of
CPU usage and weird behavior where the number of rows the optimizer
thinks it has to search through is rising slowly, even though new rows
aren't being entered into any of the tables it's searching through.
What causes this and how can I fix it? The query has not changed at
all. The data in the rows have changed, but the just values, not the
number of rows nor the indexing of the tables. It's baffling. All
tables being joined (10) are being explicitly joined using INNER JOIN
or LEFT JOIN in an order that should (at least it was) making optimal
use of the indexing.
* Philip Molter
* DataFoundry.net
* http://www.datafoundry.net/
* philip@datafoundry.net