Rural Hunter <ruralhunter@xxxxxxxxx> wrote: > 于2012年6月8日 22:10:58,Tom Lane写到: >> Rural Hunter <ruralhunter@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
