我一直在尝试调试一个永远不会完成的特别慢的查询(它需要永远并最终超时),并发现它取决于语句ORDER BY
:如果它在那里,它永远不会完成,如果我删除它,它会立即返回。
我的假设是该字段没有索引,但是我发现有一个:
CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
但这似乎没有任何区别,所以我想知道原因是什么?这可能是因为它是一个“唯一索引”,与该表上的其他索引不同?
请参阅下面的查询:
永远不会完成:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
ORDER BY "counter" ASC
LIMIT 200
立即完成:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
LIMIT 200
该表上的索引:
changes | changes_id_index | CREATE INDEX changes_id_index ON public.changes USING btree (id)
changes | changes_id_unique | CREATE UNIQUE INDEX changes_id_unique ON public.changes USING btree (id)
changes | changes_item_id_index | CREATE INDEX changes_item_id_index ON public.changes USING btree (item_id)
changes | changes_pkey | CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
changes | changes_user_id_index | CREATE INDEX changes_user_id_index ON public.changes USING btree (user_id)
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> ORDER BY "counter" ASC
postgres-> LIMIT 200;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.56..9206.44 rows=200 width=99)
-> Index Scan using changes_pkey on changes (cost=0.56..5746031.01 rows=124834 width=99)
Index Cond: (counter > '-1'::integer)
Filter: (((user_id)::text = 'xxxxxxxx'::text) AND ((type = 1) OR (type = 3)))
(4 rows)
*解释一下快速查询:
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> LIMIT 200;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=0.56..1190.09 rows=200 width=99)
-> Index Scan using changes_user_id_index on changes (cost=0.56..742468.10 rows=124834 width=99)
Index Cond: ((user_id)::text = 'xxxxxxxx'::text)
Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
(4 rows)
知道查询速度慢的原因是什么吗?
据推测,user_id 'xxxxxxx' 缺乏“counter”值较低的行,因此当它遍历计数器索引时,它必须走很长的路才能找到其中 200 个满足 'xxxxxxx'(加上其他条件)的行。PostgreSQL 统计系统无法了解此类交互。
最好是在 上建立索引
(user_id, counter)
。这样它就可以直接跳转到正确的 user_id,然后只扫描那些,但已经按顺序扫描。更好的是过滤索引:
但这属于“每个查询一个索引”类别,因此我会坚持使用更通用的类别,除非您确实需要额外的性能。