将ORDER BY从id更改为另一个索引列（具有较低的LIMIT）会产生巨大的成本

小编典典

将ORDER BY从id更改为另一个索引列（具有较低的LIMIT）会产生巨大的成本

sql

我有一个关于500 000行表的查询。

基本上

WHERE s3_.id = 287
ORDER BY m0_.id DESC
LIMIT 25

=>查询运行时间= 20ms

WHERE s3_.id = 287
ORDER BY m0_.created_at DESC
LIMIT 25

=>查询运行时间= 15000ms或更长时间

在created_at上有一个索引。

查询计划是完全不同的。

不幸的是，我不是一个查询计划专家。我想在created_at订购时重现快速查询计划。

那有可能吗，我将如何处理呢？

查询计划-慢速查询 （按m0_.created_at排序）：http
://explain.depesz.com/s/KBl

查询计划-快速查询 （按m0_.id排序）：http
://explain.depesz.com/s/2pYZ

完整查询

SELECT m0_.id AS id0, m0_.content AS content1, m0_.created_at AS created_at2,
c1_.id AS id3, l2_.id AS id4, l2_.reference AS reference5,
s3_.id AS id6, s3_.name AS name7, s3_.code AS code8,
u4_.email AS email9, u4_.id AS id10, u4_.firstname AS firstname11, u4_.lastname AS lastname12,
u5_.email AS email13, u5_.id AS id14, u5_.firstname AS firstname15, u5_.lastname AS lastname16,
g6_.id AS id17, g6_.firstname AS firstname18, g6_.lastname AS lastname19, g6_.email AS email20,
m0_.conversation_id AS conversation_id21, m0_.author_user_id AS author_user_id22, m0_.author_guest_id AS author_guest_id23,
c1_.author_user_id AS author_user_id24, c1_.author_guest_id AS author_guest_id25, c1_.listing_id AS listing_id26,
l2_.poster_id AS poster_id27, l2_.site_id AS site_id28, l2_.building_id AS building_id29, l2_.type_id AS type_id30, l2_.neighborhood_id AS neighborhood_id31, l2_.facility_bathroom_id AS facility_bathroom_id32, l2_.facility_kitchen_id AS facility_kitchen_id33, l2_.facility_heating_id AS facility_heating_id34, l2_.facility_internet_id AS facility_internet_id35, l2_.facility_condition_id AS facility_condition_id36, l2_.original_translation_id AS original_translation_id37, 
u4_.site_id AS site_id38, u4_.address_id AS address_id39, u4_.billing_address_id AS billing_address_id40,
u5_.site_id AS site_id41, u5_.address_id AS address_id42, u5_.billing_address_id AS billing_address_id43,
g6_.site_id AS site_id44
FROM message m0_
INNER JOIN conversation c1_ ON m0_.conversation_id = c1_.id
INNER JOIN listing l2_ ON c1_.listing_id = l2_.id
INNER JOIN Site s3_ ON l2_.site_id = s3_.id
INNER JOIN user_ u4_ ON l2_.poster_id = u4_.id
LEFT JOIN user_ u5_ ON m0_.author_user_id = u5_.id
LEFT JOIN guest_data g6_ ON m0_.author_guest_id = g6_.id
WHERE s3_.id = 287
ORDER BY m0_.created_at DESC
LIMIT 25 OFFSET 0

阅读 183

2021-04-28

共1个答案

小编典典

原来是索引问题。查询的NULLS行为与索引不一致。

CREATE INDEX message_created_at_idx on message (created_at DESC NULLS LAST);

... ORDER BY message.created_at DESC; -- defaults to NULLS FIRST when DESC

解决方案

如果在索引或查询中指定NULLS，请确保它们彼此一致。

即：ASC NULLS LAST与ASC NULLS LAST或一致DESC NULLS FIRST。

倒数最后

CREATE INDEX message_created_at_idx on message (created_at DESC NULLS LAST);

... ORDER BY messsage.created_at DESC NULLS LAST;

首先为空

CREATE INDEX message_created_at_idx on message (created_at DESC); -- defaults to NULLS FIRST when DESC

... ORDER BY messsage.created_at DESC -- defaults to NULLS FIRST when DESC;

非空

如果您的列不是NOT NULL，请不要理会NULLS。

CREATE INDEX message_created_at_idx on message (created_at DESC);

... ORDER BY messsage.created_at DESC;

2021-04-28