Message-ID: <4FD226E6.2060705@gmail.com>
Date: Sat, 09 Jun 2012 00:23:02 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:13.0) Gecko/20120604 Thunderbird/13.0
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: how to change the index chosen in plan?
References: <4FD1FF14.9060303@gmail.com> <11881.1339164658@sss.pgh.pa.us>
	<4FD20AD0.3040902@gmail.com>
	<4FD1C7C702000025000481E7@gw.wicourts.gov>
In-Reply-To: <4FD1C7C702000025000481E7@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi Kevin,
Thanks for your detailed explanation.

于 2012/6/8 22:37, Kevin Grittner 写道:
> Rural Hunter <ruralhunter@gmail.com> wrote:
>> 于2012年6月8日 22:10:58,Tom Lane写到:
>>> Rural Hunter <ruralhunter@gmail.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
>