我有两个相对复杂的 SQL 查询,我使用UNION ALL
. 每个单独的查询都很快并立即返回。问题是,一旦连接在一起,它们的性能就会非常糟糕,而且经常会超时。
这是完整的查询:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'USER_ID'
ORDER BY "counter" ASC
LIMIT 100
) as sub1
UNION ALL
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND type = 2
AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
ORDER BY "counter" ASC
LIMIT 100
) as sub2
ORDER BY counter ASC -- SLOW!!
LIMIT 100;
经过一番挖掘后,我发现速度慢的原因是ORDER BY counter ASC
语句末尾的(而不是内部语句中的那些 - 那些都很好)。如果存在,则需要超过一分钟并超时。如果没有它,它会在 5ms 内返回。
这对我来说没有任何意义,因为到那时我们总共有 200 行,因此排序应该非常快(特别是因为行已经排序!)。
我尝试分析这些查询,但没有什么对我来说很突出:
- 完整查询(需要超过 60 秒)
Limit (cost=1.70..325537.14 rows=100 width=101)
-> Merge Append (cost=1.70..651072.57 rows=200 width=101)
Sort Key: changes.counter
-> Limit (cost=0.56..106310.10 rows=100 width=101)
-> Index Scan using changes_pkey on changes (cost=0.56..4162018.71 rows=3915 width=101)
Index Cond: (counter > '-1'::integer)
Filter: (((user_id)::text = 'USER_ID'::text) AND ((type = 1) OR (type = 3)))
-> Limit (cost=1.12..544758.47 rows=100 width=101)
-> Nested Loop (cost=1.12..11516171.34 rows=2114 width=101)
-> Index Scan using changes_pkey on changes changes_1 (cost=0.56..3986383.73 rows=10843703 width=101)
Index Cond: (counter > '-1'::integer)
Filter: (type = 2)
-> Index Only Scan using user_items_user_id_item_id_unique on user_items (cost=0.56..0.69 rows=1 width=24)
Index Cond: ((user_id = 'USER_ID'::text) AND (item_id = (changes_1.item_id)::text))
- 没有最后一个的完整查询
ORDER BY
(需要 5 毫秒):
Limit (cost=23934.97..180049.76 rows=100 width=101)
-> Append (cost=23934.97..336164.56 rows=200 width=101)
-> Limit (cost=23934.97..23935.22 rows=100 width=101)
-> Sort (cost=23934.97..23944.76 rows=3915 width=101)
Sort Key: changes.counter
-> Bitmap Heap Scan on changes (cost=284.11..23785.34 rows=3915 width=101)
Recheck Cond: ((user_id)::text = 'USER_ID'::text)
Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
-> Bitmap Index Scan on changes_user_id_index (cost=0.00..283.13 rows=6209 width=0)
Index Cond: ((user_id)::text = 'USER_ID'::text)
-> Limit (cost=312214.83..312226.33 rows=100 width=101)
-> Gather Merge (cost=312214.83..312357.89 rows=1244 width=101)
Workers Planned: 1
-> Sort (cost=311214.82..311217.93 rows=1244 width=101)
Sort Key: changes_1.counter
-> Nested Loop (cost=148.64..311167.28 rows=1244 width=101)
-> Parallel Bitmap Heap Scan on user_items (cost=148.07..11209.94 rows=1785 width=24)
Recheck Cond: ((user_id)::text = 'USER_ID'::text)
-> Bitmap Index Scan on user_items_user_id_index (cost=0.00..147.31 rows=3034 width=0)
Index Cond: ((user_id)::text = 'USER_ID'::text)
-> Index Scan using changes_item_id_index on changes changes_1 (cost=0.56..167.91 rows=13 width=101)
Index Cond: ((item_id)::text = (user_items.item_id)::text)
Filter: ((counter > '-1'::integer) AND (type = 2))
我想我可以删除“order by”语句并自己在代码中对它们进行排序,但这感觉不太干净。
知道可以采取什么措施来改善这一点吗?