我有这个查询(在postgresql):
SELECT "table_1".* FROM "table_1"
INNER JOIN "join_table"
ON "table_1"."id" = "join_table"."table_1_id"
WHERE "join_table"."table_2_id" = 650727
ORDER BY table_1.created_at DESC
LIMIT 1
返回1个结果，但是执行
需要250-300毫秒。在table_1.created_at、join_table.table_1_id和join_table.table_2_id上存在b树索引
当我只从查询中删除LIMIT 1时，执行时间下降到~13ms。这个特定的查询目前只返回一个结果(没有LIMIT)，但是在WHERE中有其他具有不同值的查询可能返回更多结果(这就是为什么需要使用LIMIT)。
为什么在一个已经只返回一个结果的查询中添加LIMIT会增加执行时间?
以下是LIMIT 1的解释计划(这些对我来说总是很难完全理解…):http://explain.depesz.com/s/rOy
下面是没有LIMIT 1的解释计划:http://explain.depesz.com/s/q3d7
此外，如果我保留LIMIT 1，但将顺序更改为ASC，查询也会下降到13毫秒。如果我将LIMIT更改为LIMIT 20(但保留ORDER BY DESC)，则只需要22ms…wtf ! ?
所以它与ORDER BY DESC和LIMIT 1 (Exactly 1)的组合有关
好，这是一个非常经典的例子。
无论何时使用LIMIT(或类似的FETCH FIRST ... ROWS ONLY)，优化器都会尝试优化查询，以便尽可能快地获取第一行。这意味着优化器优先选择第一个成本值较低的执行计划，而不是执行计划中显示的第二个成本值。请记住:PostgreSQL显示的两个成本值(例如，cost=48.150..6,416.240)是设置成本(48.150)和总执行成本(6,416.240)。
这里的"问题"是，你有一个索引支持你的ORDER BY子句。因此，PostgreSQL认为它可以遍历这个索引(由于查询中的DESC修饰符，顺序相反)，并检查其他表中的每一行是否满足其他WHERE子句。问题是优化器无法知道这将是第一行还是最后一行(根据ORDER BY)。优化器进行任意猜测，并认为匹配行更接近开始而不是结束。这个乐观的估计然后被用来计算成本值，结果是过于乐观，以至于PostgreSQL最终确定了一个糟糕的执行计划。
当您将ORDER BY ... DESC更改为ORDER BY ... ASC时，优化器会进行相同的任意但乐观的估计，结果在这种情况下更为正确，因此您可以获得更好的执行时间。
然而，从优化的角度来看，根本原因是优化器估计有2,491行将匹配WHERE子句tango = 650727。当优化器能够正确地估计这只是命中几行时，那么问题可能就不会发生。
WHERE子句是足够琐碎的，一个好的估计应该没有问题。所以，主要的问题是:你在那张表上的统计数据如何?
有几种方法可以解决这个问题:
- 更新你的统计数据(
ANALYZE)，看看这是否有帮助。 - 增加为该列(
ALTER TABLE ... SET STATISTICS)存储的最常见值的数量。这也增加了用于收集统计数据的样本量，这意味着ANALYZE需要更长的时间，但产生更准确的结果。
理论上，这应该足以解决这个问题。但是，其他选项是:
- 如果你不需要索引
created_at的其他原因(如其他查询)，摆脱它。 - 重写查询，使错误的执行计划不再是选项。特别是，如果您能够编写查询，使
ORDER BY子句使用与WHERE子句相同的表，那将是非常棒的:如果幸运的话，您可能在join_table中有一个列与table_1.created_at具有相同的顺序，这样就不会对您的顺序产生任何影响。但是，要小心，这很容易出错(例如，序列填充的序列号可能有大纲)。
虽然您只添加了限制1，但是对查询的任何更改都会影响其执行计划和使用的索引。
解决您的问题，因为您说当订单是ASC时，您的查询性能很好:
似乎在table_1上创建的索引。created_at为ASC。我知道在db2中，您可以在创建索引时指定为双向ASC/DESC。我猜在postgresql中你应该有相同的，如果没有的话，你可以在同一个字段上创建两个索引一个是sort DESC，另一个是sort ASC