From: Rural Hunter <ruralhunter@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: how to change the index chosen in plan?
Date: Sat, 09 Jun 2012 00:23:02 +0800
Message-ID: <4FD226E6.2060705@gmail.com> (raw)
In-Reply-To: <4FD1C7C702000025000481E7@gw.wicourts.gov>
References: <4FD1FF14.9060303@gmail.com>
<11881.1339164658@sss.pgh.pa.us>
<4FD20AD0.3040902@gmail.com>
<4FD1C7C702000025000481E7@gw.wicourts.gov>
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/SlowQueryQuestionsSorry 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruralhunter@gmail.com, Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us
Subject: Re: how to change the index chosen in plan?
In-Reply-To: <4FD226E6.2060705@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox