优化器选择一个不合理的计划 [英] Optimizer picks an ineffient plan
问题描述
我有一个客户表，其字段为CUSTOMER_ID作为主键
（cust_pkkey），该表中有102,834条记录。
以下select语句有效罚款：
从customer_id客户订单中选择*;
QUERY PLAN：
使用客户的cust_pkkey进行索引扫描（成本= 0.00..5175.17行= 102834
宽度= 724）
总运行时间：5999.47毫秒
但是......
从客户订单中选择* by customer_id，first_name;
QUERY PLAN：
排序（成本= 142028.25..142028.25行= 102834 width = 724）
- > Seq Scan on customer（成本= 0.00..4617.34行= 102834宽度= 724）
总运行时间：19999.81毫秒
似乎优化器应该能够检测到（在这个例子中，至少
）第一个按字段排序是主键，不应该考虑其他字段，因为它没有意义......结果集将在
< primary key>订单。
注意：我正在Postgresql 7.2 for Windows上测试这个，所以我已经处理了
。
谢谢并继续做好工作!!
I have a customer table that has the field CUSTOMER_ID as the primary key
(cust_pkkey), the table has 102,834 records in it.
The following select statement works fine:
select * from customer order by customer_id;
QUERY PLAN:
Index Scan using cust_pkkey on customer (cost=0.00..5175.17 rows=102834
width=724)
Total runtime: 5999.47 msec
but...
select * from customer order by customer_id, first_name;
QUERY PLAN:
Sort(cost=142028.25..142028.25 rows=102834 width=724)
-> Seq Scan on customer (cost=0.00..4617.34 rows=102834 width=724)
Total runtime: 19999.81 msec
It seems that the optimizer should be able to detect (in this instance at
least) that the first order by field is a primary key and should not
consider the other fields because it''s pointless... the resultset will be in
<primary key> order.
NOTE: I''m testing this on Postgresql 7.2 for Windows, so this my have
already been dealt with.
Thanks and keep up the great work!!
推荐答案
" Bupp Phillips" <他*** @ noname.com>写道：
"Bupp Phillips" <he***@noname.com> writes:
但是......
从客户订单中选择* by customer_id，first_name;
查询计划：
排序（成本） = 142028.25..142028.25行= 102834宽度= 724）
- > Seq Scan on customer（成本= 0.00..4617.34行= 102834宽度= 724）
总运行时间：19999.81毫秒
but...
select * from customer order by customer_id, first_name;
QUERY PLAN:
Sort(cost=142028.25..142028.25 rows=102834 width=724)
-> Seq Scan on customer (cost=0.00..4617.34 rows=102834 width=724)
Total runtime: 19999.81 msec
实际上在这种情况下优化器会可能仍然使用顺序扫描
，即使它有一个它认为可以使用的索引。如果你要阅读整张桌子，那么无论如何它都会更快地阅读它，而不是跳过所有
即使你必须排序它。
然而你确实有一个观点。在这种情况下，我不认为postgres甚至
考虑使用索引。即使在这种情况下决定不使用它，也可能会出现想要使用它的情况。
但是我是我不确定在很多情况下会出现这种情况。即使在
自动生成的代码中，这是多余事物的常见原因，这样我也不会认为我已经看到过这种特殊的组合了。
-
greg
---------------------- -----（播出结束）---------------------------
提示6：你有没有？搜索了我们的列表档案？
http://archives.postgresql.org
Actually in this case the optimizer would likely still use a sequential scan
even if it had an index it thought it could use. If you''re going to be reading
the whole table anyways it''ll be faster to read it in order than to jump all
around even if you have to sort it.
However you do have a point. In this case I don''t think postgres even
considers using the index. Even if it would decide not to use it in this case
there could conceivably be cases where it would want to use it.
However I''m not sure I see a lot of cases where this would come up. Even in
automatically generated code, which is the usual cause of redundant things
like this, I don''t think I''ve seen this particular combination ever come up.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Greg Stark< gs ***** @ mit.edu>写道：
Greg Stark <gs*****@mit.edu> writes:
" Bupp Phillips" <他*** @ noname.com>写道：
"Bupp Phillips" <he***@noname.com> writes:
select * from customer order by customer_id，first_name;
[其中customer_id是主键]
select * from customer order by customer_id, first_name;
[ where customer_id is the primary key ]
但是你确实有一个观点。在这种情况下，我不认为postgres甚至会考虑使用索引。
它不会，因为索引似乎没有提供正确的排序
订单。
但是我是不确定我会发现很多情况会出现这种情况。
However you do have a point. In this case I don''t think postgres even
considers using the index.
It will not, since the index does not appear to provide the correct sort
order.
However I''m not sure I see a lot of cases where this would come up.
是的，这才是问题的真正症结所在。优化器是否应该花费* b $ b循环*每个*查询以检测用户编写的情况
无用的排序键？我非常怀疑这是一场胜利。 ISTM这样的优惠会给那些写好自己的查询的人带来好处。
让那些粗心大意的人受益。
问候，汤姆车道
---------------------------（播出结束）----- ----------------------
提示8：解释分析是你的朋友
Yes, that''s the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I''ve got grave doubts that it''s a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
嗯，不幸的是你有这样的感觉，因为SQL Server正确地处理它
。
" Tom Lane" < tg*@sss.pgh.pa.us>在消息中写道
news：43 ************* @ssh.pgh.pa.us ...
Well, it''s unfortunate that you feel that way, because SQL Server handles it
correctly.
"Tom Lane" <tg*@sss.pgh.pa.us> wrote in message
news:43*************@sss.pgh.pa.us...
Greg Stark< gs*****@mit.edu>写道：
Greg Stark <gs*****@mit.edu> writes:
" Bupp Phillips" <他*** @ noname.com>写道：
"Bupp Phillips" <he***@noname.com> writes:
select * from customer order by customer_id，first_name;
[其中customer_id是主键]
select * from customer order by customer_id, first_name;
[ where customer_id is the primary key ]
但是你确实有一点意义。在这种情况下，我不认为postgres甚至会考虑使用索引。
However you do have a point. In this case I don''t think postgres even
considers using the index.
它不会，因为索引似乎没有提供正确的排序
订单。
It will not, since the index does not appear to provide the correct sort
order.
但是我不确定我会发现很多情况会出现这种情况。
However I''m not sure I see a lot of cases where this would come up.
是的，那个'这是问题的真正症结所在。优化器是否应该在* every *查询上花费周期来检测用户编写无用排序键的情况？我非常怀疑这是一场胜利。 ISTM这样的优化会惩罚那些写好自己的疑问的人，以帮助那些粗心大意的人。
问候，汤姆巷
--- ------------------------（播出结束）--------------------- ------
提示8：解释分析是你的朋友
Yes, that''s the real crux of the matter. Should the optimizer spend
cycles on *every* query to detect cases where the user has written
useless sort keys? I''ve got grave doubts that it''s a win. ISTM such
an optimization penalizes the folk who write their queries well to
benefit those who are careless.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
这篇关于优化器选择一个不合理的计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
