Message-ID: <4FD5EAB2.7030807@gmail.com>
Date: Mon, 11 Jun 2012 20:55:14 +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: pgsql-performance@postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: how to change the index chosen in plan?
References: <4FD5994702000025000482F4@gw.wicourts.gov>
In-Reply-To: <4FD5994702000025000482F4@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

于 2012/6/11 20:07, Kevin Grittner 写道:
> Rural Hunter  wrote:
>> 于 2012/6/9 22:39, Kevin Grittner 写道:
>   
>>> You neglected to mention the LIMIT clause in your earlier
>>> presentation of the problem. A LIMIT can have a big impact on plan
>>> choice. Is the LIMIT 10 part of the actual query you want to
>>> optimize? Either way it would be helpful to see the EXPLAIN
>>> ANALYZE output for the the query without the LIMIT clause.
>> Yes, sorry for that. I do need the limit clause in the query to
>> show only part of the results to the user(common multi-pages view).
>> Without the limit clause, I got the plan as I wanted:
>> http://explain.depesz.com/s/Qdu
>>
>> So looks either I remove the order-by or limit clause, I can get
>> what I wanted. But I do need the both in the query...
>   
> Well, we're still doing diagnostic steps.  What this one shows is
> that your statistics are leading the planner to believe that there
> will be 20846 rows with lid = 3072, while there are really only 62.
> If it knew the actual number I doubt it would choose the slower plan.
>   
> The next thing I would try is:
>   
> ALTER TABLE article_label ALTER COLUMN lid SET STATISTICS = 5000;
> ANALYZE article_label;
>   
> Then try the query without LIMIT and see if you get something on the
> right order of magnitude comparing the estimated rows to actual on
> that index scan.  You can try different STATISTICS values until you
> get the lowest value that puts the estimate in the right
> neighborhood.  Higher settings will increase plan time; lower
> settings may lead to bad plans.
>   
> Once you've got a decent estimate, try with the ORDER BY and LIMIT
> again.
I set statistics to 5000 and got estimated row count 559. Set statistics 
to 8000 and got estimated row count 393.  At this step, I run the query 
with both order-by and limit clause and got the expected result.
Kevin, Thank you very much for your patience and step-by-step guidance! 
I learnt a lot from this case!
>   
> If you have a hard time getting a good estimate even with a high
> statistics target, you should investigate whether you have extreme
> table bloat.
>   
> -Kevin
>