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.
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
