pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to change the index chosen in plan?
12+ messages / 3 participants
[nested] [flat]
* how to change the index chosen in plan?
@ 2012-06-08 13:33 Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rural Hunter @ 2012-06-08 13:33 UTC (permalink / raw)
To: pgsql-performance
I have a query like this:
select a.* from a inner join b on a.aid=b.aid where a.col1=33 a.col2=44
and b.bid=8
postgresql selected the index on a.col1 then selected the index on
b.bid. But in my situation, I know that the query will be faster if it
chose the index on b.bid first since there are only a few rows with
value 8. So I re-wrote the query as below:
select a.* from a where a.aid in (select aid from b where bid=8) and
a.col1=33 a.col2=44
But surprisingly, postgresql didn't change the plan. it still chose to
index scan on a.col1. How can I re-wirte the query so postgresql will
scan on b.bid first?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-08 14:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2012-06-08 14:10 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-performance
Rural Hunter <ruralhunter@gmail.com> writes:
> I have a query like this:
> select a.* from a inner join b on a.aid=b.aid where a.col1=33 a.col2=44
> and b.bid=8
> postgresql selected the index on a.col1 then selected the index on
> b.bid. But in my situation, I know that the query will be faster if it
> chose the index on b.bid first since there are only a few rows with
> value 8.
If you know that and the planner doesn't, maybe ANALYZE is called for.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-08 14:23 Rural Hunter <ruralhunter@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Rural Hunter @ 2012-06-08 14:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
No, it's not the analyze problem. For some other values on b.bid such
as 9, 10, the plan is fine since there a a lot of rows in table b for
them. But for some specific values such as 8 I want the plan changed.
于2012年6月8日 22:10:58,Tom Lane写到:
> Rural Hunter <ruralhunter@gmail.com> writes:
>> I have a query like this:
>> select a.* from a inner join b on a.aid=b.aid where a.col1=33 a.col2=44
>> and b.bid=8
>> postgresql selected the index on a.col1 then selected the index on
>> b.bid. But in my situation, I know that the query will be faster if it
>> chose the index on b.bid first since there are only a few rows with
>> value 8.
>
> If you know that and the planner doesn't, maybe ANALYZE is called for.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-08 14:37 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2012-06-08 14:37 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Rural Hunter <ruralhunter@gmail.com> wrote:
> 于2012年6月8日 22:10:58,Tom Lane写到:
>> Rural Hunter <ruralhunter@gmail.com> writes:
>>> I have a query like this:
>>> select a.* from a inner join b on a.aid=b.aid where a.col1=33
>>> a.col2=44 and b.bid=8
>>> postgresql selected the index on a.col1 then selected the index
>>> on b.bid. But in my situation, I know that the query will be
>>> faster if it chose the index on b.bid first since there are only
>>> a few rows with value 8.
>>
>> If you know that and the planner doesn't, maybe ANALYZE is called
>> for.
>>
> No, it's not the analyze problem.
So you ran ANALYZE and retried? If not, please do.
> For some other values on b.bid such as 9, 10, the plan is fine
> since there a a lot of rows in table b for them.
So it uses the same plan regardless of the number of rows in table b
for the value? That sure *sounds* like you need to run ANALYZE,
possibly after adjusting the statistics target for a column or two.
> But for some specific values such as 8 I want the plan changed.
If you approach it from that line of thought, you will be unlikely
to reach a good long-term solution. PostgreSQL has a costing model
to determine which plan is expected to be cheapest (fastest). This
is based on statistics gathered during ANALYZE and on costing
factors. Generally, if it's not choosing the fastest plan, you
aren't running ANALYZE frequently enough or with a fine-grained
enough statistics target _or_ you need to adjust your costing
factors to better model your actual costs.
You haven't given us a lot of clues about which it is that you need
to do, but there is *some* suggestion that you need to ANALYZE. If
you *try* that and it doesn't solve your problem, please read this
page and provide more information:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
-Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-08 16:23 Rural Hunter <ruralhunter@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 12+ messages in thread
From: Rural Hunter @ 2012-06-08 16:23 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Hi Kevin,
Thanks for your detailed explanation.
于 2012/6/8 22:37, Kevin Grittner 写道:
> Rural Hunter <ruralhunter@gmail.com> wrote:
>> 于2012年6月8日 22:10:58,Tom Lane写到:
>>> Rural Hunter <ruralhunter@gmail.com> writes:
>>>> I have a query like this:
>>>> select a.* from a inner join b on a.aid=b.aid where a.col1=33
>>>> a.col2=44 and b.bid=8
>>>> postgresql selected the index on a.col1 then selected the index
>>>> on b.bid. But in my situation, I know that the query will be
>>>> faster if it chose the index on b.bid first since there are only
>>>> a few rows with value 8.
>>> If you know that and the planner doesn't, maybe ANALYZE is called
>>> for.
>>>
>> No, it's not the analyze problem.
>
> So you ran ANALYZE and retried? If not, please do.
Yes, I did.
>
>> For some other values on b.bid such as 9, 10, the plan is fine
>> since there a a lot of rows in table b for them.
>
> So it uses the same plan regardless of the number of rows in table b
> for the value?
yes.
> That sure *sounds* like you need to run ANALYZE,
> possibly after adjusting the statistics target for a column or two.
How can adjust the statistics target?
>
>> But for some specific values such as 8 I want the plan changed.
>
> If you approach it from that line of thought, you will be unlikely
> to reach a good long-term solution. PostgreSQL has a costing model
> to determine which plan is expected to be cheapest (fastest). This
> is based on statistics gathered during ANALYZE and on costing
> factors. Generally, if it's not choosing the fastest plan, you
> aren't running ANALYZE frequently enough or with a fine-grained
> enough statistics target _or_ you need to adjust your costing
> factors to better model your actual costs.
>
> You haven't given us a lot of clues about which it is that you need
> to do, but there is *some* suggestion that you need to ANALYZE. If
> you *try* that and it doesn't solve your problem, please read this
> page and provide more information:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
Sorry the actual tables and query are very complicated so I just
simplified the problem with my understanding. I rechecked the query and
found it should be simplified like this:
select a.* from a inner join b on a.aid=b.aid where a.col1=33 and
a.col2=44 and a.time<now() and b.bid=8 order by a.time limit 10
There is an index on (a.col1,a.col2,a.time). If I remove the order-by
clause, I can get the plan as I expected. I think that's why postgresql
selected that index. But still I want the index on b.bid selected first
for value 8 since there are only several rows with bid 8. though for
other normal values there might be several kilo to million rows.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-08 16:39 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Rural Hunter <ruralhunter@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2012-06-08 16:39 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
Rural Hunter <ruralhunter@gmail.com> wrote:
> How can adjust the statistics target?
default_statistics_target
http://www.postgresql.org/docs/current/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OT...
or ALTER TABLE x ALTER COLUMN y SET STATISTICS n
http://www.postgresql.org/docs/current/interactive/sql-altertable.html
> Sorry the actual tables and query are very complicated so I just
> simplified the problem with my understanding. I rechecked the
> query and found it should be simplified like this:
> select a.* from a inner join b on a.aid=b.aid where a.col1=33 and
> a.col2=44 and a.time<now() and b.bid=8 order by a.time limit 10
> There is an index on (a.col1,a.col2,a.time). If I remove the
> order-by clause, I can get the plan as I expected. I think that's
> why postgresql selected that index.
Sounds like it expects the sort to be expensive, which means it
probably expects a large number of rows. An EXPLAIN ANALYZE of the
query with and without the ORDER BY might be instructive. It would
also help to know what version of PostgreSQL you have and how it is
configured, all of which shows up in the results of the query on
this page:
http://wiki.postgresql.org/wiki/Server_Configuration
> But still I want the index on b.bid selected first
> for value 8 since there are only several rows with bid 8. though
> for other normal values there might be several kilo to million
> rows.
An EXPLAIN ANALYZE of one where you think the plan is a good choice
might also help.
Oh, and just to be sure -- are you actually running queries with the
literals like you show, or are you using prepared statements with
placeholders and plugging the values in after the statement is
prepared? Sample code, if possible, might help point to or
eliminate issues with a cached plan. If you're running through a
cached plan, there is no way for it to behave differently based on
the value plugged into the query -- the plan has already been set
before you get to that point.
-Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-09 02:08 Rural Hunter <ruralhunter@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 12+ messages in thread
From: Rural Hunter @ 2012-06-09 02:08 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
于 2012/6/9 0:39, Kevin Grittner 写道:
> Rural Hunter <ruralhunter@gmail.com> wrote:
>
>> How can adjust the statistics target?
>
> default_statistics_target
>
> http://www.postgresql.org/docs/current/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OT...
>
> or ALTER TABLE x ALTER COLUMN y SET STATISTICS n
>
> http://www.postgresql.org/docs/current/interactive/sql-altertable.html
Thanks, I will check detail.
>
>> Sorry the actual tables and query are very complicated so I just
>> simplified the problem with my understanding. I rechecked the
>> query and found it should be simplified like this:
>> select a.* from a inner join b on a.aid=b.aid where a.col1=33 and
>> a.col2=44 and a.time<now() and b.bid=8 order by a.time limit 10
>> There is an index on (a.col1,a.col2,a.time). If I remove the
>> order-by clause, I can get the plan as I expected. I think that's
>> why postgresql selected that index.
>
> Sounds like it expects the sort to be expensive, which means it
> probably expects a large number of rows. An EXPLAIN ANALYZE of the
> query with and without the ORDER BY might be instructive. It would
> also help to know what version of PostgreSQL you have and how it is
> configured, all of which shows up in the results of the query on
> this page:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
Here is the output:
name | current_setting
-----------------------------+---------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
archive_command | test ! -f /dbbk/postgres/logarch/%f.gz && gzip -c %p
>/dbbk/postgres/logarch/%f.gz
archive_mode | on
autovacuum | on
autovacuum_freeze_max_age | 2000000000
checkpoint_segments | 20
client_encoding | UTF8
effective_cache_size | 150GB
full_page_writes | off
lc_collate | zh_CN.utf8
lc_ctype | zh_CN.utf8
listen_addresses | *
log_autovacuum_min_duration | 30min
log_destination | stderr
log_line_prefix | %t [%u@%h]
log_min_duration_statement | 10s
log_statement | ddl
logging_collector | on
maintenance_work_mem | 10GB
max_connections | 2500
max_stack_depth | 2MB
max_wal_senders | 1
port | 3500
server_encoding | UTF8
shared_buffers | 60GB
synchronous_commit | off
TimeZone | PRC
track_activities | on
track_counts | on
vacuum_freeze_table_age | 1000000000
wal_buffers | 16MB
wal_level | hot_standby
work_mem | 8MB
(33 rows)
>> But still I want the index on b.bid selected first
>> for value 8 since there are only several rows with bid 8. though
>> for other normal values there might be several kilo to million
>> rows.
>
> An EXPLAIN ANALYZE of one where you think the plan is a good choice
> might also help.
Ok, I get out a simple version of the actualy query. Here is the explain
anaylze without order-by, which is I wanted:
http://explain.depesz.com/s/p1p
Another with the order-by which I want to avoid:
http://explain.depesz.com/s/ujU
This is the count of rows in article_label with value 3072(which I
referred as table b in previous mail):
# select count(*) from article_label where lid=3072;
count
-------
56
(1 row)
>
> Oh, and just to be sure -- are you actually running queries with the
> literals like you show, or are you using prepared statements with
> placeholders and plugging the values in after the statement is
> prepared? Sample code, if possible, might help point to or
> eliminate issues with a cached plan. If you're running through a
> cached plan, there is no way for it to behave differently based on
> the value plugged into the query -- the plan has already been set
> before you get to that point.
Yes, I ran the query directly wih psql.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-09 14:39 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2012-06-09 14:39 UTC (permalink / raw)
To: ruralhunter@gmail.com; +Cc: pgsql-performance; tgl@sss.pgh.pa.us
Rural Hunter wrote:
> 于 2012/6/9 0:39, Kevin Grittner 写道:
> name | current_setting
> full_page_writes | off
There may be exceptions on some file systems, but generally turning
this off leaves you vulnerable to possible database corruption if you
OS or hardware crashes.
> max_connections | 2500
Yikes! You may want to look in to a connection pooler which can take
2500 client connections and funnel them into a much smaller number of
database connections.
https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
> shared_buffers | 60GB
You might want to compare your performance with this setting against
a smaller setting. Many benchmarks have shown settings about a
certain point (like 8MB to 12 MB) to be counter-productive, although
a few have shown increased performance going past that. It really
seems to depend on your hardware and workload, so you have to test to
find the "sweet spot" for your environment.
> work_mem | 8MB
With so many connections, I can understand being this low. One of
the advantages of using connection pooling to funnel your user
connections into fewer database conncections is that you can boost
this, which might help considerably with some types of queries.
None of the above, however, really gets to your immediate problem.
What is most significant about your settings with regard to the
problem query is what's *not* in that list. You appear to have a
heavily cached active data set, based on the row counts and timings
in EXPLAIN ANALYZE output, and you have not adjusted your cost
factors, which assume less caching.
Try setting these on a connection and then running your queries on
that connection.
set seq_page_cost = 0.1;
set random_page_cost = 0.1;
set cpu_tuple_cost = 0.03;
> Ok, I get out a simple version of the actualy query. Here is the
> explain anaylze without order-by, which is I wanted:
> http://explain.depesz.com/s/p1p
>
> Another with the order-by which I want to avoid:
> http://explain.depesz.com/s/ujU
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.
-Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-09 15:38 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 12+ messages in thread
From: Kevin Grittner @ 2012-06-09 15:38 UTC (permalink / raw)
To: ruralhunter@gmail.com; Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance; tgl@sss.pgh.pa.us
"Kevin Grittner" wrote:
>> shared_buffers | 60GB
>
> You might want to compare your performance with this setting against
> a smaller setting. Many benchmarks have shown settings about a
> certain point (like 8MB to 12 MB) to be counter-productive, although
> a few have shown increased performance going past that. It really
> seems to depend on your hardware and workload, so you have to test to
> find the "sweet spot" for your environment.
Er, I meant "8GB to 12GB", not MB. Sorry.
-Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-11 04:46 Rural Hunter <ruralhunter@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 12+ messages in thread
From: Rural Hunter @ 2012-06-11 04:46 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance; tgl@sss.pgh.pa.us
于 2012/6/9 22:39, Kevin Grittner 写道:
> Rural Hunter wrote:
>> 于 2012/6/9 0:39, Kevin Grittner 写道:
>
>> name | current_setting
>
>> full_page_writes | off
>
> There may be exceptions on some file systems, but generally turning
> this off leaves you vulnerable to possible database corruption if you
> OS or hardware crashes.
Yes, I understand. My situation is, the io utiliztion of my system is
quite high so I turned this off to reduce the io utilization. We have a
replication server to serve as the hot standby if there is any issue on
the primary. So currently I think it's acceptable option to me.
>
>> max_connections | 2500
>
> Yikes! You may want to look in to a connection pooler which can take
> 2500 client connections and funnel them into a much smaller number of
> database connections.
>
> https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
>
>> shared_buffers | 60GB
>
> You might want to compare your performance with this setting against
> a smaller setting. Many benchmarks have shown settings about a
> certain point (like 8MB to 12 MB) to be counter-productive, although
> a few have shown increased performance going past that. It really
> seems to depend on your hardware and workload, so you have to test to
> find the "sweet spot" for your environment.
>
>> work_mem | 8MB
>
> With so many connections, I can understand being this low. One of
> the advantages of using connection pooling to funnel your user
> connections into fewer database conncections is that you can boost
> this, which might help considerably with some types of queries.
>
> None of the above, however, really gets to your immediate problem.
> What is most significant about your settings with regard to the
> problem query is what's *not* in that list. You appear to have a
> heavily cached active data set, based on the row counts and timings
> in EXPLAIN ANALYZE output, and you have not adjusted your cost
> factors, which assume less caching.
Thanks for the advices. As of now we don't see overall performance issue
on the db. I will adjust these settings based on your advices if we
begin to see overall performance degrade.
>
> Try setting these on a connection and then running your queries on
> that connection.
>
> set seq_page_cost = 0.1;
> set random_page_cost = 0.1;
> set cpu_tuple_cost = 0.03;
I tried these settings but don't see noticeable improvement. The plan is
not changed.
>
>> Ok, I get out a simple version of the actualy query. Here is the
>> explain anaylze without order-by, which is I wanted:
>> http://explain.depesz.com/s/p1p
>>
>> Another with the order-by which I want to avoid:
>> http://explain.depesz.com/s/ujU
>
> 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...
>
> -Kevin
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-11 12:07 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2012-06-11 12:07 UTC (permalink / raw)
To: ruralhunter@gmail.com; +Cc: pgsql-performance; tgl@sss.pgh.pa.us
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: how to change the index chosen in plan?
@ 2012-06-11 12:55 Rural Hunter <ruralhunter@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 12+ messages in thread
From: Rural Hunter @ 2012-06-11 12:55 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance; tgl@sss.pgh.pa.us
于 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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2012-06-11 12:55 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-08 13:33 how to change the index chosen in plan? Rural Hunter <ruralhunter@gmail.com>
2012-06-08 14:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-06-08 14:23 ` Rural Hunter <ruralhunter@gmail.com>
2012-06-08 14:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-08 16:23 ` Rural Hunter <ruralhunter@gmail.com>
2012-06-08 16:39 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-09 02:08 ` Rural Hunter <ruralhunter@gmail.com>
2012-06-09 14:39 Re: how to change the index chosen in plan? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-11 04:46 ` Rural Hunter <ruralhunter@gmail.com>
2012-06-09 15:38 Re: how to change the index chosen in plan? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-11 12:07 Re: how to change the index chosen in plan? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-11 12:55 ` Rural Hunter <ruralhunter@gmail.com>
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