這個賞金已經結束。此問題的答案有資格獲得 50聲望賞金。賞金寬限期在13 小時后結束。 用戶想引起更多關注這個問題：
當子查詢太慢時，我正在尋找改進的性能，每個表中有數百萬行并選擇數十萬個專案進行匯總。
語境
我有一個使用 PostgreSQL 資料庫的 Django REST API，其中包含數百萬個專案。這些專案由多個系統處理，處理細節被發回并存盤在記錄表中。簡化模型為：
class Item(models.Model):
details = models.JSONField()
class Record(models.Model):
items = models.ManyToManyField(Item)
created = models.DateTimeField(auto_created=True)
system = models.CharField(max_length=100)
status = models.CharField(max_length=100)
details = models.JSONField()
目標
我想對 Items 表進行任意過濾并獲得各種處理系統的摘要。此摘要獲??取每個系統的每個選定專案的最新狀態，并顯示每個狀態的計數。例如，如果我過濾 1055 個專案，則示例回傳是：
{
System_1: [running: 5, completed: 1000, error: 50],
System_2: [halted: 55, completed: 1000],
System_3: [submitted: 1055]
}
我目前正在執行如下查詢，它回傳 System_1 的處理狀態計數并為其他系統重復并打包到 JSON 回傳中。
Item.objects.filter(....).annotate(
system_1_status=Subquery(
Record.objects.filter(
system='System_1',
items__id=OuterRef('pk')
).order_by('-created').values('status')[:1]
)
).values('system_1_status').annotate(count=Count('system_1_status'))
這將轉換為 sql 查詢：
SELECT
"api_item"."id",
"api_item"."details",
(
SELECT
U0."status"
FROM
"api_record" U0
INNER JOIN
"api_record_items" U1
ON
(U0."id" = U1."record_id")
WHERE
(U1."item_id" = ("api_item"."id") AND U0."system" = system_1)
ORDER BY
U0."created" DESC LIMIT 1
) AS "system_1_status"
FROM "api_item"
我們有數以百萬計的專案和記錄，如果我們選擇少于一千個專案，這將相當有效。在此之上需要幾分鐘。試圖為成千上萬的專案做這件事是災難性的。
問題
我可以提高這個查詢的性能嗎？除了玩索引之外，我看不到怎么做？
或者，將 JSONField 添加到存盤該專案的每個系統的最新狀態快取的專案模型中是否是一個壞主意？雖然我不喜歡復制資料的想法，但在查詢時對 Item 模型上已有的欄位進行聚合應該非常快。我有 DjangoQ，我可以使用預定函式來保持這些欄位是最新的。
uj5u.com熱心網友回復：
下面的解決方案將我的示例的查詢時間從 5 分鐘減少到 20 秒。
from collections import Counter
items = Item.objects.filter(...)
{
"System_1": Counter(
items.
filter(record__system='System_1').
order_by('id', '-record__created').
values_list('record__status', flat=True).
distinct('id')),
"System_2": Counter(
items.
filter(record__system='System_2').
order_by('id', '-record__created').
values_list('record__status', flat=True).
distinct('id'))
}
下面給出了生成的 PostgreSQL 計劃，如果可以改進，請告訴我：
SELECT DISTINCT ON ("api_item"."id") "api_record"."status"
FROM "api_item" INNER JOIN "api_record_items" ON ("api_item"."id" = "api_record_items"."item_id")
INNER JOIN "api_record" ON ("api_record_items"."record_id" = "api_record"."id")
WHERE "api_record"."system" = System_1 ORDER BY "api_item"."id" ASC, "api_record"."created" DESC
我不喜歡我需要從資料庫中提取所有值來計算它們，但是我無法讓聚合與確保每個專案只計算一條記錄所需的不同值一起作業。
如果有人可以改進這一點，查詢計劃是：
Unique (cost=563327.33..1064477.67 rows=1010100 width=21) (actual time=16194.180..22301.422 rows=1010100 loops=1)
Planning Time: 0.492 ms
Execution Time: 22401.646 ms
-> Gather Merge (cost=563327.33..1053946.33 rows=4212534 width=21) (actual time=16194.179..21165.116 rows=22200000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=562327.31..566715.36 rows=1755222 width=21) (actual time=16140.068..17729.869 rows=7400000 loops=3)
Worker 1: Sort Method: external merge Disk: 244080kB
Worker 0: Sort Method: external merge Disk: 246880kB
Sort Method: external merge Disk: 247800kB
" Sort Key: api_item.id, api_record.created DESC"
-> Parallel Hash Join (cost=22117.30..308287.51 rows=1755222 width=21) (actual time=5719.348..8826.655 rows=7400000 loops=3)
Hash Cond: (api_record_items.item_id = api_item.id)
-> Parallel Hash Join (cost=2584.61..261932.34 rows=1755222 width=21) (actual time=17.042..3748.984 rows=7400000 loops=3)
-> Parallel Hash (cost=12626.75..12626.75 rows=420875 width=8) (actual time=180.939..180.940 rows=336700 loops=3)
Hash Cond: (api_record_items.record_id = api_record.id)
Buckets: 131072 Batches: 16 Memory Usage: 3520kB
-> Parallel Seq Scan on api_record_items (cost=0.00..234956.18 rows=9291718 width=16) (actual time=0.472..1557.711 rows=7433333 loops=3)
-> Parallel Seq Scan on api_item (cost=0.00..12626.75 rows=420875 width=8) (actual time=0.022..95.567 rows=336700 loops=3)
-> Parallel Hash (cost=2402.11..2402.11 rows=14600 width=21) (actual time=16.479..16.480 rows=10012 loops=3)
Buckets: 32768 Batches: 1 Memory Usage: 1952kB
-> Parallel Seq Scan on api_record (cost=0.00..2402.11 rows=14600 width=21) (actual time=6.063..13.094 rows=10012 loops=3)
Rows Removed by Filter: 33340
Filter: ((system)::text = 'system_1'::text)
uj5u.com熱心網友回復：
您正在為每個專案執行一個子查詢，嘗試執行以下操作并使用聚合。
from django.db.models import Case, When, Sum
items = Item.objects.filter(# your condition)
results = Record.objects.values("system").annotate(
running=Sum(Case(When(status="running", then=1), default=0,
output_field=IntegerField())),
completed=Sum(Case(When(status="completed", then=1), default=0,
output_field=IntegerField())),
# add more status annotations
).order_by("system").filter(items__in=items)
uj5u.com熱心網友回復：
我認為一個簡單的group by可能對獲取狀態有用；
from django.db.models import Count
Item.objects.filter(record__system='System_1').values('record__status').annotate(c=Count('record__status')).values('record__status', 'c')
uj5u.com熱心網友回復：
它看起來像一個經典的top-n-per-group問題。您想獲取每個專案的最新狀態。
如果您無法創建合適的索引并且api_item表中有很多行，那么最有效的方法很可能是使用ROW_NUMBER視窗函式，就像這樣。我從問題中重新撰寫了您的查詢以產生相同的結果，但希望更有效。
WITH
CTE_rn
AS
(
SELECT
U1."item_id"
,U0."status" AS "system_1_status"
,ROW_NUMBER() OVER (PARTITION BY U1."item_id" ORDER BY U0."created" DESC) AS rn
FROM
"api_record" U0
INNER JOIN "api_record_items" U1 ON U0."id" = U1."record_id"
WHERE
U0."system" = 'system_1'
)
,CTE_item_status
AS
(
SELECT
"item_id"
,"system_1_status"
FROM
CTE_rn
WHERE
rn = 1
)
SELECT
"api_item"."id"
,"api_item"."details"
,CTE_item_status."system_1_status"
FROM
"api_item"
LEFT JOIN CTE_item_status ON CTE_item_status."item_id" = "api_item"."id"
;
如果您知道永遠不會api_items沒有任何狀態，或者您不想在結果集中看到此類專案，請INNER JOIN CTE_item_status在主查詢中使用而不是LEFT JOIN.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/493993.html