Rural Hunter <ruralhunter(at)gmail(dot)com> wrote:
> 于2012年6月8日 22:10:58,Tom Lane写到:
>> Rural Hunter <ruralhunter(at)gmail(dot)com> writes:
>>> I have a query like this:
>>> select a.* from a inner join b on a.aid=b.aid where a.col1=33
>>> a.col2=44 and b.bid=8
>>> postgresql selected the index on a.col1 then selected the index
>>> on b.bid. But in my situation, I know that the query will be
>>> faster if it chose the index on b.bid first since there are only
>>> a few rows with value 8.
>>
>> If you know that and the planner doesn't, maybe ANALYZE is called
>> for.
>>
> No, it's not the analyze problem.
So you ran ANALYZE and retried? If not, please do.
> For some other values on b.bid such as 9, 10, the plan is fine
> since there a a lot of rows in table b for them.
So it uses the same plan regardless of the number of rows in table b
for the value? That sure *sounds* like you need to run ANALYZE,
possibly after adjusting the statistics target for a column or two.
> But for some specific values such as 8 I want the plan changed.
If you approach it from that line of thought, you will be unlikely
to reach a good long-term solution. PostgreSQL has a costing model
to determine which plan is expected to be cheapest (fastest). This
is based on statistics gathered during ANALYZE and on costing
factors. Generally, if it's not choosing the fastest plan, you
aren't running ANALYZE frequently enough or with a fine-grained
enough statistics target _or_ you need to adjust your costing
factors to better model your actual costs.
You haven't given us a lot of clues about which it is that you need
to do, but there is *some* suggestion that you need to ANALYZE. If
you *try* that and it doesn't solve your problem, please read this
page and provide more information:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
-Kevin