explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lcdy

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 171.672 5,509.479 ↑ 1.7 17,490 1

GroupAggregate (cost=80,972,013.65..83,703,663.00 rows=29,174 width=154) (actual time=5,271.384..5,509.479 rows=17,490 loops=1)

  • 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=1135601 read=35331 dirtied=46 written=440, temp read=1698 written=2003
2.          

CTE request_filtered

3. 1,896.339 1,896.339 ↑ 16.7 17,490 1

Index Scan using requests_target_full_idx on requests r_1 (cost=0.70..1,163,112.98 rows=291,737 width=1,007) (actual time=0.492..1,896.339 rows=17,490 loops=1)

  • Index Cond: ((target)::text = ANY ('{2.25.70979651869268635392983113873997187447,2.25.202973438196673271781698759096192781730,2.25.245218126112063527195048982063928033008,2.25.153873976349002842206331
  • Filter: (((status)::text = 'PENDING'::text) OR ((status)::text <> 'PENDING'::text))
  • Buffers: shared hit=2944 read=12856 dirtied=46 written=149
4. 370.330 5,337.807 ↑ 770.8 94,493 1

Sort (cost=79,808,900.67..79,990,981.45 rows=72,832,313 width=154) (actual time=5,271.336..5,337.807 rows=94,493 loops=1)

  • 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: external merge Disk: 13552kB
  • Buffers: shared hit=1135601 read=35331 dirtied=46 written=440, temp read=1698 written=2003
5. 135.728 4,967.477 ↑ 770.8 94,493 1

Nested Loop Left Join (cost=0.12..53,494,341.48 rows=72,832,313 width=154) (actual time=1.268..4,967.477 rows=94,493 loops=1)

  • Buffers: shared hit=1135589 read=35331 dirtied=46 written=440, temp written=305
6. 1,928.409 1,928.409 ↑ 16.7 17,490 1

CTE Scan on request_filtered r (cost=0.00..5,834.74 rows=291,737 width=134) (actual time=0.494..1,928.409 rows=17,490 loops=1)

  • Buffers: shared hit=2944 read=12856 dirtied=46 written=149, temp written=305
7. 2,483.580 2,903.340 ↑ 62.6 5 17,490

Append (cost=0.12..180.21 rows=313 width=28) (actual time=0.152..0.166 rows=5 loops=17,490)

  • Buffers: shared hit=1132645 read=22475 written=291
  • -> Index Scan using request_attrs_partition_current_request_id_idx8 on request_attrs_partition_270861535 a_10 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=17490
  • -> Index Scan using request_attrs_partition_current_request_id_idx9 on request_attrs_partition_276335522 a_11 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=17490
  • -> Index Scan using request_attrs_partition_current_request_id_idx10 on request_attrs_partition_281464835 a_12 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx11 on request_attrs_partition_286926621 a_13 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx12 on request_attrs_partition_292448696 a_14 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx13 on request_attrs_partition_297922775 a_15 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx14 on request_attrs_partition_303578283 a_16 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx15 on request_attrs_partition_309209601 a_17 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx16 on request_attrs_partition_314634834 a_18 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx17 on request_attrs_partition_320280087 a_19 (cost=0.44..8.24 rows=8 width=29) (actual time=0.002..0.002 rows=0 loops=1749
  • -> Index Scan using request_attrs_partition_current_request_id_idx18 on request_attrs_partition_326017480 a_20 (cost=0.44..8.24 rows=8 width=29) (actual time=0.094..0.099 rows=5 loops=1749
8. 17.490 17.490 ↓ 0.0 0 17,490

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

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=17490
9. 52.470 52.470 ↓ 0.0 0 17,490

Index Scan using request_attrs_pk on request_attrs_partition_221562697 a_1 (cost=0.58..15.59 rows=166 width=28) (actual time=0.003..0.003 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=69960
10. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx on request_attrs_partition_226912529 a_2 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
11. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx1 on request_attrs_partition_232613849 a_3 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
12. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx2 on request_attrs_partition_238458194 a_4 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
13. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx3 on request_attrs_partition_244124150 a_5 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
14. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx4 on request_attrs_partition_249551379 a_6 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
15. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx5 on request_attrs_partition_254853149 a_7 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
16. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx6 on request_attrs_partition_260281345 a_8 (cost=0.44..8.22 rows=7 width=28) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
17. 34.980 34.980 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx7 on request_attrs_partition_265641364 a_9 (cost=0.44..8.22 rows=7 width=29) (actual time=0.002..0.002 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52470
  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=48691 read=21178 written=276
18. 69.960 69.960 ↓ 0.0 0 17,490

Index Scan using request_attrs_partition_current_request_id_idx19 on request_attrs_partition_current a_21 (cost=0.43..8.22 rows=7 width=29) (actual time=0.004..0.004 rows=0 loops=17,490)

  • Index Cond: (r.request_id = request_id)
  • Buffers: shared hit=52044 read=1297 written=15