Re: how to change the index chosen in plan?

Hi Kevin,
Thanks for your detailed explanation.

于 2012/6/8 22:37, Kevin Grittner 写道:
> 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.
Yes, I did.
>
>> 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?
yes.
> That sure *sounds* like you need to run ANALYZE,
> possibly after adjusting the statistics target for a column or two.
How can adjust the statistics target?
>
>> 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
Sorry the actual tables and query are very complicated so I just
simplified the problem with my understanding. I rechecked the query and
found it should be simplified like this:
select a.* from a inner join b on a.aid=b.aid where a.col1=33 and
a.col2=44 and a.time<now() and b.bid=8 order by a.time limit 10
There is an index on (a.col1,a.col2,a.time). If I remove the order-by
clause, I can get the plan as I expected. I think that's why postgresql
selected that index. But still I want the index on b.bid selected first
for value 8 since there are only several rows with bid 8. though for
other normal values there might be several kilo to million rows.
>
> -Kevin
>