Re: how to change the index chosen in plan?
От | Rural Hunter |
---|---|
Тема | Re: how to change the index chosen in plan? |
Дата | |
Msg-id | 4FD226E6.2060705@gmail.com обсуждение исходный текст |
Ответ на | Re: how to change the index chosen in plan? ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Ответы |
Re: how to change the index chosen in plan?
("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
|
Список | pgsql-performance |
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 >
В списке pgsql-performance по дате отправления: