explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2QRh

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 1,584.383 ↑ 1.0 250 1

Limit (cost=220,429.98..220,430.61 rows=250 width=793) (actual time=1,584.362..1,584.383 rows=250 loops=1)

2.          

CTE basic_app_data

3. 90.215 90.215 ↑ 1.0 401,665 1

Seq Scan on assignment_approver ap (cost=0.00..14,601.84 rows=407,485 width=36) (actual time=0.007..90.215 rows=401,665 loops=1)

4.          

CTE final_app_data

5. 209.335 879.867 ↓ 6.2 249,934 1

HashAggregate (cost=66,432.35..66,832.35 rows=40,000 width=36) (actual time=832.979..879.867 rows=249,934 loops=1)

  • Group Key: x.assignment_id, array_agg(x.app_id) OVER (?)
6. 265.533 670.532 ↑ 1.0 401,665 1

WindowAgg (cost=57,263.94..64,394.93 rows=407,485 width=36) (actual time=361.180..670.532 rows=401,665 loops=1)

7. 234.728 404.999 ↑ 1.0 401,665 1

Sort (cost=57,263.94..58,282.65 rows=407,485 width=36) (actual time=361.163..404.999 rows=401,665 loops=1)

  • Sort Key: x.assignment_id
  • Sort Method: external merge Disk: 8448kB
8. 170.271 170.271 ↑ 1.0 401,665 1

CTE Scan on basic_app_data x (cost=0.00..8,149.70 rows=407,485 width=36) (actual time=0.008..170.271 rows=401,665 loops=1)

9.          

CTE assignments

10. 25.890 1,285.072 ↑ 1.1 122,335 1

Hash Left Join (cost=24,570.32..31,295.12 rows=129,578 width=168) (actual time=995.235..1,285.072 rows=122,335 loops=1)

  • Hash Cond: (a_1.id = aa.assignment_id)
11. 25.550 1,258.970 ↑ 1.1 121,799 1

Hash Left Join (cost=24,536.17..28,180.33 rows=129,578 width=139) (actual time=995.019..1,258.970 rows=121,799 loops=1)

  • Hash Cond: (a_1.guide_id = g.id)
12. 124.009 1,233.052 ↑ 1.1 121,799 1

Hash Right Join (cost=24,470.16..27,773.16 rows=129,578 width=120) (actual time=994.644..1,233.052 rows=121,799 loops=1)

  • Hash Cond: (app.assignment_id = a_1.id)
13. 947.469 947.469 ↓ 6.2 249,934 1

CTE Scan on final_app_data app (cost=0.00..800.00 rows=40,000 width=36) (actual time=832.982..947.469 rows=249,934 loops=1)

14. 34.601 161.574 ↑ 1.1 121,799 1

Hash (cost=21,078.43..21,078.43 rows=129,578 width=88) (actual time=161.574..161.574 rows=121,799 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 3680kB
15. 126.973 126.973 ↑ 1.1 121,799 1

Seq Scan on assignment a_1 (cost=0.00..21,078.43 rows=129,578 width=88) (actual time=0.017..126.973 rows=121,799 loops=1)

  • Filter: ((parentassignment_id IS NULL) AND ((assignee_id IS NOT NULL) OR (group_id IS NOT NULL)) AND ((status)::text <> 'DELEGATED_COMPLETE'::text) AND (id <> 662901) AND ((status)::text <> ALL ('{CANCELLED,SUSPENDED,FINISHED_INCOMPLETE}'::text[])))
  • Rows Removed by Filter: 323753
16. 0.159 0.368 ↓ 1.0 1,424 1

Hash (cost=48.23..48.23 rows=1,423 width=23) (actual time=0.368..0.368 rows=1,424 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
17. 0.209 0.209 ↓ 1.0 1,424 1

Seq Scan on guide g (cost=0.00..48.23 rows=1,423 width=23) (actual time=0.009..0.209 rows=1,424 loops=1)

18. 0.107 0.212 ↓ 1.0 1,192 1

Hash (cost=19.62..19.62 rows=1,162 width=4) (actual time=0.212..0.212 rows=1,192 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 58kB
19. 0.105 0.105 ↓ 1.0 1,192 1

Seq Scan on assignment_associates aa (cost=0.00..19.62 rows=1,162 width=4) (actual time=0.005..0.105 rows=1,192 loops=1)

20. 54.433 1,584.370 ↑ 103.7 250 1

Sort (cost=107,700.67..107,765.46 rows=25,916 width=793) (actual time=1,584.361..1,584.370 rows=250 loops=1)

  • Sort Key: a.duedatetime DESC, a.guide_title COLLATE "C.UTF-8", a.id
  • Sort Method: top-N heapsort Memory: 147kB
21. 51.338 1,529.937 ↓ 4.7 121,799 1

Group (cost=101,614.92..106,538.88 rows=25,916 width=793) (actual time=1,455.622..1,529.937 rows=121,799 loops=1)

  • Group Key: a.id, a.title, a.duedatetime, a.dtype, a.group_id, a.assignee_id, a.guide_id, a.guide_title, a.assigner_id, a.completeddate, a.approveallatonce, a.approvalrequired, a.status, (array_to_string(a.app_ids, ', '::text))
22. 99.763 1,478.599 ↑ 1.1 122,335 1

Sort (cost=101,614.92..101,938.86 rows=129,578 width=761) (actual time=1,455.618..1,478.599 rows=122,335 loops=1)

  • Sort Key: a.id, a.title, a.duedatetime DESC, a.dtype, a.group_id, a.assignee_id, a.guide_id, a.guide_title, a.assigner_id, a.completeddate, a.approveallatonce, a.approvalrequired, a.status, (array_to_string(a.app_ids, ', '::text))
  • Sort Method: external merge Disk: 14912kB
23. 1,378.836 1,378.836 ↑ 1.1 122,335 1

CTE Scan on assignments a (cost=0.00..2,915.51 rows=129,578 width=761) (actual time=995.242..1,378.836 rows=122,335 loops=1)