From: Rural Hunter <ruralhunter@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: how to change the index chosen in plan?
Date: Mon, 11 Jun 2012 20:55:14 +0800
Message-ID: <4FD5EAB2.7030807@gmail.com> (raw)
In-Reply-To: <4FD5994702000025000482F4@gw.wicourts.gov>
References: <4FD5994702000025000482F4@gw.wicourts.gov>
于 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
>
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: <4FD5EAB2.7030807@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