于 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
