Skip site navigation (1) Skip section navigation (2)

Re: how to change the index chosen in plan?

From: Rural Hunter <ruralhunter(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: how to change the index chosen in plan?
Date: 2012-06-08 16:23:02
Message-ID: 4FD226E6.2060705@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>



In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2012-06-08 16:39:38
Subject: Re: how to change the index chosen in plan?
Previous:From: Kevin GrittnerDate: 2012-06-08 14:37:11
Subject: Re: how to change the index chosen in plan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group