Re: how to change the index chosen in plan?

From: Rural Hunter <ruralhunter(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org, tgl(at)sss(dot)pgh(dot)pa(dot)us
Subject: Re: how to change the index chosen in plan?
Date: 2012-06-11 12:55:14
Message-ID: 4FD5EAB2.7030807@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Shaun Thomas 2012-06-11 13:42:52 Re: Performance of CLUSTER
Previous Message Kevin Grittner 2012-06-11 12:07:51 Re: how to change the index chosen in plan?