Очень высокая стоимость запроса Hasura (Postgres)
Я использую Hasura для своего проекта, и у меня есть следующий запрос GraphQL, который извлекает необходимую информацию о водителе:
query GetChosenDriver($driverId: String!, $dateFrom: timestamptz!, $dateTo: timestamptz!) {
user_by_pk(id: $driverId) {
id
name
email
deliveredOrdersCount(args: {date_from: $dateFrom, date_to: $dateTo})
rescheduledOrdersCount(args: {date_from: $dateFrom, date_to: $dateTo})
returnedOrdersCount(args: {date_from: $dateFrom, date_to: $dateTo})
track_points(where: {time: {_gte: $dateFrom, _lt: $dateTo}}, order_by: {time: asc}) {
id
time
lng
lat
batteryLevel
}
order_changes(where: {createdAt: {_gte: $dateFrom, _lt: $dateTo}, new: {_has_key: "statusName"}, editorId: {_eq: $driverId}, geo_event: {}}, order_by: {createdAt: asc}) {
id
orderId
old
new
createdAt
geo_event {
id
lat
lng
createdAt
}
}
orders(where: {_or: [{order_changes: {editorId: {_eq: $driverId}, createdAt: {_gte: $dateFrom, _lt: $dateTo}}}, {plannedDate: {_gte: $dateFrom, _lt: $dateTo}}], statusName: {_neq: "RequestChangeCourierService"}}, order_by: {plannedDate: asc}) {
id
plannedDate
plannedDateDuration
shortCode
statusName
client {
address {
country
state
street
city
}
}
}
}
}
Анализ запроса показал следующие данные:
-> Nested Loop Left Join (cost=33708742.87..33708750.98 rows=1 width=394)
-> Nested Loop Left Join (cost=33670192.10..33670200.18 rows=1 width=362)
-> Nested Loop Left Join (cost=33611961.63..33611969.68 rows=1 width=330)
-> Index Scan using user_pkey on "user" (cost=0.27..8.29 rows=1 width=298)
Index Cond: (id = 'a5d883f7-eb67-4dab-859c-e2059deb647f'::text)
-> Aggregate (cost=33611961.36..33611961.37 rows=1 width=32)
-> Nested Loop Left Join (cost=33604654.21..33611955.90 rows=437 width=40)
-> Sort (cost=33604653.63..33604654.72 rows=437 width=316)
Sort Key: "order"."plannedDate"
-> Index Scan using "order_driverId_idx" on "order" (cost=0.42..33604634.46 rows=437 width=316)
Index Cond: ("driverId" = "user".id)
Filter: (("statusName" <> 'RequestChangeCourierService'::text) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (("plannedDate" < '2022-06-07 05:00:00+00'::timestamp with time zone) AND ("plannedDate" >= '2022-06-06 05:00:00+00'::timestamp with time zone))))
SubPlan 5
-> Seq Scan on order_change _1__be_0_order_change (cost=0.00..38533.80 rows=1 width=0)
Filter: (("createdAt" < '2022-06-07 05:00:00+00'::timestamp with time zone) AND ("createdAt" >= '2022-06-06 05:00:00+00'::timestamp with time zone) AND ("orderId" = "order".id) AND ("editorId" = 'a5d883f7-eb67-4dab-859c-e2059deb647f'::text))
SubPlan 6
-> Seq Scan on order_change _1__be_0_order_change_1 (cost=0.00..36412.95 rows=1 width=4)
Filter: (("createdAt" < '2022-06-07 05:00:00+00'::timestamp with time zone) AND ("createdAt" >= '2022-06-06 05:00:00+00'::timestamp with time zone) AND ("editorId" = 'a5d883f7-eb67-4dab-859c-e2059deb647f'::text))
-> Nested Loop Left Join (cost=0.58..16.68 rows=1 width=32)
-> Limit (cost=0.29..8.31 rows=1 width=136)
-> Index Scan using client_pkey on client (cost=0.29..8.31 rows=1 width=136)
Index Cond: (id = "order"."clientId")
-> Subquery Scan on "_4_root.ar.root.orders.or.client.or.address.base" (cost=0.29..8.33 rows=1 width=32)
-> Limit (cost=0.29..8.31 rows=1 width=221)
-> Index Scan using address_pkey on address (cost=0.29..8.31 rows=1 width=221)
Index Cond: (id = client."addressId")
SubPlan 2
-> Result (cost=0.00..0.01 rows=1 width=32)
SubPlan 3
-> Result (cost=0.00..0.01 rows=1 width=32)
SubPlan 4
-> Result (cost=0.00..0.01 rows=1 width=32)
-> Aggregate (cost=58230.47..58230.48 rows=1 width=32)
-> Subquery Scan on "_12_root.ar.root.track_points.base" (cost=58228.14..58229.69 rows=62 width=40)
-> Sort (cost=58228.14..58228.30 rows=62 width=64)
Sort Key: track_point."time"
-> Seq Scan on track_point (cost=0.00..58226.30 rows=62 width=64)
Filter: (("time" < '2022-06-07 05:00:00+00'::timestamp with time zone) AND ("time" >= '2022-06-06 05:00:00+00'::timestamp with time zone) AND ("user".id = "userId"))
SubPlan 7
-> Result (cost=0.00..0.01 rows=1 width=32)
-> Aggregate (cost=38550.77..38550.78 rows=1 width=32)
-> Nested Loop Left Join (cost=38542.68..38550.76 rows=1 width=40)
-> Sort (cost=38542.26..38542.27 rows=1 width=157)
Sort Key: order_change."createdAt"
-> Result (cost=0.42..38542.25 rows=1 width=157)
One-Time Filter: ("user".id = 'a5d883f7-eb67-4dab-859c-e2059deb647f'::text)
-> Nested Loop (cost=0.42..38542.25 rows=1 width=125)
-> Seq Scan on order_change (cost=0.00..38533.80 rows=1 width=125)
Filter: (("createdAt" < '2022-06-07 05:00:00+00'::timestamp with time zone) AND ("createdAt" >= '2022-06-06 05:00:00+00'::timestamp with time zone) AND (new ? 'statusName'::text) AND ("editorId" = 'a5d883f7-eb67-4dab-859c-e2059deb647f'::text))
-> Index Only Scan using "geo_event_orderChangeId_key" on geo_event _16__be_0_geo_event (cost=0.42..8.44 rows=1 width=4)
Index Cond: ("orderChangeId" = order_change.id)
-> Subquery Scan on "_18_root.ar.root.order_changes.or.geo_event.base" (cost=0.42..8.46 rows=1 width=32)
-> Limit (cost=0.42..8.44 rows=1 width=33)
-> Index Scan using "geo_event_orderChangeId_key" on geo_event (cost=0.42..8.44 rows=1 width=33)
Index Cond: ("orderChangeId" = order_change.id)
SubPlan 8
-> Result (cost=0.00..0.01 rows=1 width=32)
SubPlan 9
-> Result (cost=0.00..0.01 rows=1 width=32)
SubPlan 1
-> Result (cost=0.00..0.76 rows=1 width=32)
Я вижу, что стоимость резко возрастает на этом участке: -> Index Scan using "order_driverId_idx" on "order" (cost=0.42..33604634.46 rows=437 width=316), однако мне так и не удалось выяснить, что именно так повышает стоимость.
Есть ли предположения или рекомендации, почему именно так происходит и как это исправить?
UPDATE
Также проанализировал с помощью запроса explain(analyze, buffers) <запрос>:
Aggregate (cost=32060240.36..32060240.38 rows=1 width=32) (actual time=3315.979..3316.032 rows=1 loops=1)
Buffers: shared hit=3866 read=118940
-> Nested Loop Left Join (cost=32060232.14..32060239.60 rows=1 width=394) (actual time=2698.755..2698.845 rows=1 loops=1)
Buffers: shared hit=2174 read=61748
-> Nested Loop Left Join (cost=32022816.73..32022824.15 rows=1 width=362) (actual time=2571.294..2571.351 rows=1 loops=1)
Buffers: shared hit=995 read=42556
-> Nested Loop Left Join (cost=31966492.48..31966499.87 rows=1 width=330) (actual time=1404.751..1404.803 rows=1 loops=1)
Buffers: shared hit=867 read=19224
-> Seq Scan on "user" (cost=0.00..7.36 rows=1 width=298) (actual time=1245.595..1245.630 rows=1 loops=1)
Filter: (id = ' a5d883f7-eb67-4dab-859c-e2059deb647f'::text)
Rows Removed by Filter: 188
Buffers: shared hit=5
-> Aggregate (cost=31966492.48..31966492.49 rows=1 width=32) (actual time=159.139..159.154 rows=1 loops=1)
Buffers: shared hit=862 read=19224
-> Nested Loop Left Join (cost=31959319.11..31966487.11 rows=429 width=40) (actual time=158.264..159.046 rows=49 loops=1)
Buffers: shared hit=862 read=19224
-> Sort (cost=31959318.52..31959319.60 rows=429 width=316) (actual time=158.114..158.123 rows=49 loops=1)
Sort Key: "order"."plannedDate"
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=568 read=19224
-> Index Scan using "order_driverId_idx" on "order" (cost=0.29..31959299.77 rows=429 width=316) (actual time=157.493..157.988 rows=49 loops=1)
Index Cond: ("driverId" = "user".id)
Filter: (("statusName" <> 'RequestChangeCourierService'::text) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (("plannedDate" < '2022-06-07 08:00:00+03'::timestamp with time zone) AND ("plannedDate" >= '2022-06-06 08:00:00+03'::timestamp with time zone))))
Rows Removed by Filter: 220
Buffers: shared hit=568 read=19224
SubPlan 5
-> Seq Scan on order_change _1__be_0_order_change (cost=0.00..37375.76 rows=1 width=0) (never executed)
Filter: (("createdAt" < '2022-06-07 08:00:00+03'::timestamp with time zone) AND ("createdAt" >= '2022-06-06 08:00:00+03'::timestamp with time zone) AND ("orderId" = "order".id) AND ("editorId" = ' a5d883f7-eb67-4dab-859c-e2059deb647f'::text))
SubPlan 6
-> Seq Scan on order_change _1__be_0_order_change_1 (cost=0.00..35146.79 rows=9 width=4) (actual time=130.325..156.651 rows=120 loops=1)
Filter: (("createdAt" < '2022-06-07 08:00:00+03'::timestamp with time zone) AND ("createdAt" >= '2022-06-06 08:00:00+03'::timestamp with time zone) AND ("editorId" = ' a5d883f7-eb67-4dab-859c-e2059deb647f'::text))
Rows Removed by Filter: 891468
Buffers: shared hit=320 read=19224
-> Nested Loop Left Join (cost=0.58..16.68 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=49)
Buffers: shared hit=294
-> Limit (cost=0.29..8.31 rows=1 width=136) (actual time=0.005..0.005 rows=1 loops=49)
Buffers: shared hit=147
-> Index Scan using client_pkey on client (cost=0.29..8.31 rows=1 width=136) (actual time=0.004..0.004 rows=1 loops=49)
Index Cond: (id = "order"."clientID")
Buffers: shared hit=147
-> Subquery Scan on "_4_root.ar.root.orders.or.client.or.address.base" (cost=0.29..8.33 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=49)
Buffers: shared hit=147
-> Limit (cost=0.29..8.31 rows=1 width=221) (actual time=0.004..0.004 rows=1 loops=49)
Buffers: shared hit=147
-> Index Scan using address_pkey on address (cost=0.29..8.31 rows=1 width=221) (actual time=0.004..0.004 rows=1 loops=49)
Index Cond: (id = client."addressID")
Buffers: shared hit=147
SubPlan 2
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=49)
SubPlan 3
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=49)
SubPlan 4
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=49)
-> Aggregate (cost=56324.25..56324.26 rows=1 width=32) (actual time=1166.508..1166.512 rows=1 loops=1)
Buffers: shared hit=128 read=23332
-> Subquery Scan on "_12_root.ar.root.track_points.base" (cost=56310.78..56319.76 rows=359 width=40) (actual time=1165.572..1166.273 rows=319 loops=1)
Buffers: shared hit=128 read=23332
-> Sort (cost=56310.78..56311.68 rows=359 width=64) (actual time=1165.513..1165.535 rows=319 loops=1)
Sort Key: track_point."time"
Sort Method: quicksort Memory: 49kB
Buffers: shared hit=128 read=23332
-> Seq Scan on track_point (cost=0.00..56295.55 rows=359 width=64) (actual time=1145.845..1165.259 rows=319 loops=1)
Filter: (("time" < '2022-06-07 08:00:00+03'::timestamp with time zone) AND ("time" >= '2022-06-06 08:00:00+03'::timestamp with time zone) AND ("user".id = "userID"))
Rows Removed by Filter: 1875998
Buffers: shared hit=128 read=23332
SubPlan 7
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=319)
-> Aggregate (cost=37415.42..37415.43 rows=1 width=32) (actual time=127.454..127.486 rows=1 loops=1)
Buffers: shared hit=1179 read=19192
-> Nested Loop Left Join (cost=37407.33..37415.40 rows=1 width=40) (actual time=126.381..127.354 rows=118 loops=1)
Buffers: shared hit=1179 read=19192
-> Sort (cost=37406.91..37406.91 rows=1 width=157) (actual time=126.231..126.268 rows=118 loops=1)
Sort Key: order_change."createdAt"
Sort Method: quicksort Memory: 52kB
Buffers: shared hit=707 read=19192
-> Result (cost=0.42..37406.90 rows=1 width=157) (actual time=100.258..126.175 rows=118 loops=1)
One-Time Filter: ("user".id = ' a5d883f7-eb67-4dab-859c-e2059deb647f'::text)
Buffers: shared hit=707 read=19192
-> Nested Loop (cost=0.42..37406.90 rows=1 width=125) (actual time=100.254..126.143 rows=118 loops=1)
Buffers: shared hit=707 read=19192
-> Seq Scan on order_change (cost=0.00..37375.76 rows=7 width=125) (actual time=100.188..125.718 rows=118 loops=1)
Filter: (("createdAt" < '2022-06-07 08:00:00+03'::timestamp with time zone) AND ("createdAt" >= '2022-06-06 08:00:00+03'::timestamp with time zone) AND (new ? 'statusName'::text) AND ("editorId" = ' a5d883f7-eb67-4dab-859c-e2059deb647f'::text))
Rows Removed by Filter: 891470
Buffers: shared hit=352 read=19192
-> Index Only Scan using "geo_event_orderChangeId_key" on geo_event _16__be_0_geo_event (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=118)
Index Cond: ("orderChangeId" = order_change.id)
Heap Fetches: 0
Buffers: shared hit=355
-> Subquery Scan on "_18_root.ar.root.order_changes.or.geo_event.base" (cost=0.42..8.46 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=118)
Buffers: shared hit=472
-> Limit (cost=0.42..8.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=118)
Buffers: shared hit=472
-> Index Scan using "geo_event_orderChangeId_key" on geo_event (cost=0.42..8.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=118)
Index Cond: ("orderChangeId" = order_change.id)
Buffers: shared hit=472
SubPlan 8
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=118)
SubPlan 9
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=118)
SubPlan 1
-> Result (cost=0.00..0.76 rows=1 width=32) (actual time=616.428..616.430 rows=1 loops=1)
Buffers: shared hit=1692 read=57192
Planning:
Buffers: shared hit=12
Planning Time: 3.569 ms
JIT:
Functions: 142
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 13.627 ms, Inlining 42.314 ms, Optimization 727.350 ms, Emission 499.117 ms, Total 1282.409 ms
Execution Time: 3329.857 ms