explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lY9A

Settings
# exclusive inclusive rows x rows loops node
1. 169.344 2,075.638 ↑ 1.6 18,139 1

GroupAggregate (cost=70,379,488.30..73,129,059.60 rows=28,156 width=154) (actual time=1,860.628..2,075.638 rows=18,139 loops=1)

  • Output: r.request_id, r.owner_pacs_id, r.owner_server_id, r.owner_app_id, r.scoped_seq_num, r.request_timestamp_as_epoch, r.target, r.type, r.requester_app_id, r.requester_server_id, r.status, (COALESCE(r.num
  • Group Key: r.request_id, r.owner_pacs_id, r.owner_server_id, r.owner_app_id, r.scoped_seq_num, r.request_timestamp_as_epoch, r.target, r.type, r.requester_app_id, r.requester_server_id, r.status, (COALESCE(r.
  • Buffers: shared hit=1231545 read=37036 written=222
2.          

CTE request_filtered

3. 94.969 94.969 ↑ 15.5 18,139 1

Index Scan using requests_target_full_idx on globaldicom.requests r_1 (cost=0.70..1,124,358.10 rows=281,555 width=1,183) (actual time=0.064..94.969 rows=18,139 loops=1)

  • Output: r_1.request_id, r_1.owner_pacs_id, r_1.owner_server_id, r_1.owner_app_id, r_1.scoped_seq_num, r_1.request_timestamp_as_epoch, r_1.target, r_1.type, r_1.status, r_1.requester_app_id, r_1.reques
  • Index Cond: ((r_1.target)::text = ANY ('{2.25.70979651869268635392983113873997187447,2.25.202973438196673271781698759096192781730,2.25.245218126112063527195048982063928033008,2.25.15387397634900284220
  • Filter: (((r_1.status)::text = 'PENDING'::text) OR ((r_1.status)::text <> 'PENDING'::text))
  • Buffers: shared hit=2894 read=13284 written=70
4. 236.936 1,906.294 ↑ 755.3 97,061 1

Sort (cost=69,255,130.20..69,438,406.80 rows=73,310,639 width=154) (actual time=1,860.599..1,906.294 rows=97,061 loops=1)

  • Output: r.request_id, r.owner_pacs_id, r.owner_server_id, r.owner_app_id, r.scoped_seq_num, r.request_timestamp_as_epoch, r.target, r.type, r.requester_app_id, r.requester_server_id, r.status, (COALESCE
  • Sort Key: r.request_id, r.owner_pacs_id, r.owner_server_id, r.owner_app_id, r.scoped_seq_num, r.request_timestamp_as_epoch, r.target, r.type, r.requester_app_id, r.requester_server_id, r.status, (COALES
  • Sort Method: quicksort Memory: 27071kB
  • Buffers: shared hit=1231545 read=37036 written=222
5. 133.543 1,669.358 ↑ 755.3 97,061 1

Nested Loop Left Join (cost=0.12..54,040,099.55 rows=73,310,639 width=154) (actual time=0.312..1,669.358 rows=97,061 loops=1)

  • Output: r.request_id, r.owner_pacs_id, r.owner_server_id, r.owner_app_id, r.scoped_seq_num, r.request_timestamp_as_epoch, r.target, r.type, r.requester_app_id, r.requester_server_id, r.status, COA
  • Buffers: shared hit=1231545 read=37036 written=222
6. 120.973 120.973 ↑ 15.5 18,139 1

CTE Scan on request_filtered r (cost=0.00..5,631.10 rows=281,555 width=134) (actual time=0.072..120.973 rows=18,139 loops=1)

  • Output: r.request_id, r.owner_pacs_id, r.owner_server_id, r.owner_app_id, r.scoped_seq_num, r.request_timestamp_as_epoch, r.target, r.type, r.status, r.requester_app_id, r.requester_server_i
  • Buffers: shared hit=2894 read=13284 written=70
7. 1,342.286 1,414.842 ↑ 65.0 5 18,139

Append (cost=0.12..188.66 rows=325 width=28) (actual time=0.064..0.078 rows=5 loops=18,139)

  • Buffers: shared hit=1228651 read=23752 written=152
  • -> Index Scan using request_attrs_partition_current_request_id_idx on globaldicom.request_attrs_partition_226912529 a_2 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0 l
  • -> Index Scan using request_attrs_partition_current_request_id_idx1 on globaldicom.request_attrs_partition_232613849 a_3 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx2 on globaldicom.request_attrs_partition_238458194 a_4 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx3 on globaldicom.request_attrs_partition_244124150 a_5 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx4 on globaldicom.request_attrs_partition_249551379 a_6 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx5 on globaldicom.request_attrs_partition_254853149 a_7 (cost=0.44..8.24 rows=8 width=28) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx6 on globaldicom.request_attrs_partition_260281345 a_8 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx7 on globaldicom.request_attrs_partition_265641364 a_9 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx8 on globaldicom.request_attrs_partition_270861535 a_10 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx9 on globaldicom.request_attrs_partition_276335522 a_11 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0
  • -> Index Scan using request_attrs_partition_current_request_id_idx10 on globaldicom.request_attrs_partition_281464835 a_12 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx11 on globaldicom.request_attrs_partition_286926621 a_13 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx12 on globaldicom.request_attrs_partition_292448696 a_14 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx13 on globaldicom.request_attrs_partition_297922775 a_15 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx14 on globaldicom.request_attrs_partition_303578283 a_16 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx15 on globaldicom.request_attrs_partition_309209601 a_17 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx16 on globaldicom.request_attrs_partition_314634834 a_18 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx17 on globaldicom.request_attrs_partition_320280087 a_19 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx18 on globaldicom.request_attrs_partition_326017480 a_20 (cost=0.44..8.22 rows=7 width=29) (actual time=0.008..0.012 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx19 on globaldicom.request_attrs_partition_331666157 a_21 (cost=0.44..8.23 rows=7 width=29) (actual time=0.003..0.003 rows=
  • -> Index Scan using request_attrs_partition_current_request_id_idx20 on globaldicom.request_attrs_partition_current a_22 (cost=0.43..8.25 rows=7 width=29) (actual time=0.002..0.002 rows=0
8. 18.139 18.139 ↓ 0.0 0 18,139

Index Scan using request_attrs_request_id_idx on globaldicom.request_attrs a (cost=0.12..0.14 rows=1 width=44) (actual time=0.001..0.001 rows=0 loops=18,139)

  • Output: a.attr_id, a.value, a.request_id
  • Index Cond: (r.request_id = a.request_id)
  • Buffers: shared hit=18139
9. 54.417 54.417 ↓ 0.0 0 18,139

Index Scan using request_attrs_pk on globaldicom.request_attrs_partition_221562697 a_1 (cost=0.58..15.69 rows=171 width=28) (actual time=0.003..0.003 rows=0 loops=18,139)

  • Output: a_1.attr_id, a_1.value, a_1.request_id
  • Index Cond: (r.request_id = a_1.request_id)
  • Buffers: shared hit=72556
  • Output: a_2.attr_id, a_2.value, a_2.request_id
  • Index Cond: (r.request_id = a_2.request_id)
  • Buffers: shared hit=54417
  • Output: a_3.attr_id, a_3.value, a_3.request_id
  • Index Cond: (r.request_id = a_3.request_id)
  • Buffers: shared hit=54417
  • Output: a_4.attr_id, a_4.value, a_4.request_id
  • Index Cond: (r.request_id = a_4.request_id)
  • Buffers: shared hit=54417
  • Output: a_5.attr_id, a_5.value, a_5.request_id
  • Index Cond: (r.request_id = a_5.request_id)
  • Buffers: shared hit=54417
  • Output: a_6.attr_id, a_6.value, a_6.request_id
  • Index Cond: (r.request_id = a_6.request_id)
  • Buffers: shared hit=54417
  • Output: a_7.attr_id, a_7.value, a_7.request_id
  • Index Cond: (r.request_id = a_7.request_id)
  • Buffers: shared hit=54417
  • Output: a_8.attr_id, a_8.value, a_8.request_id
  • Index Cond: (r.request_id = a_8.request_id)
  • Buffers: shared hit=54417
  • Output: a_9.attr_id, a_9.value, a_9.request_id
  • Index Cond: (r.request_id = a_9.request_id)
  • Buffers: shared hit=54417
  • Output: a_10.attr_id, a_10.value, a_10.request_id
  • Index Cond: (r.request_id = a_10.request_id)
  • Buffers: shared hit=54417
  • Output: a_11.attr_id, a_11.value, a_11.request_id
  • Index Cond: (r.request_id = a_11.request_id)
  • Buffers: shared hit=54417
  • Output: a_12.attr_id, a_12.value, a_12.request_id
  • Index Cond: (r.request_id = a_12.request_id)
  • Buffers: shared hit=54417
  • Output: a_13.attr_id, a_13.value, a_13.request_id
  • Index Cond: (r.request_id = a_13.request_id)
  • Buffers: shared hit=54417
  • Output: a_14.attr_id, a_14.value, a_14.request_id
  • Index Cond: (r.request_id = a_14.request_id)
  • Buffers: shared hit=54417
  • Output: a_15.attr_id, a_15.value, a_15.request_id
  • Index Cond: (r.request_id = a_15.request_id)
  • Buffers: shared hit=54417
  • Output: a_16.attr_id, a_16.value, a_16.request_id
  • Index Cond: (r.request_id = a_16.request_id)
  • Buffers: shared hit=54417
  • Output: a_17.attr_id, a_17.value, a_17.request_id
  • Index Cond: (r.request_id = a_17.request_id)
  • Buffers: shared hit=54417
  • Output: a_18.attr_id, a_18.value, a_18.request_id
  • Index Cond: (r.request_id = a_18.request_id)
  • Buffers: shared hit=54417
  • Output: a_19.attr_id, a_19.value, a_19.request_id
  • Index Cond: (r.request_id = a_19.request_id)
  • Buffers: shared hit=54417
  • Output: a_20.attr_id, a_20.value, a_20.request_id
  • Index Cond: (r.request_id = a_20.request_id)
  • Buffers: shared hit=50630 read=21186 written=126
  • Output: a_21.attr_id, a_21.value, a_21.request_id
  • Index Cond: (r.request_id = a_21.request_id)
  • Buffers: shared hit=53443 read=2499 written=26
  • Output: a_22.attr_id, a_22.value, a_22.request_id
  • Index Cond: (r.request_id = a_22.request_id)
  • Buffers: shared hit=54377 read=67