explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dudy

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 6,136.859 ↑ 1.0 1 1

Result (cost=790,496.17..790,496.18 rows=1 width=32) (actual time=6,136.859..6,136.859 rows=1 loops=1)

2.          

Initplan (for Result)

3. 7.522 6,136.857 ↑ 1.0 1 1

Aggregate (cost=790,496.16..790,496.17 rows=1 width=32) (actual time=6,136.857..6,136.857 rows=1 loops=1)

4. 184.206 6,129.335 ↑ 5.8 80,626 1

Finalize GroupAggregate (cost=719,221.70..784,627.52 rows=469,491 width=187) (actual time=5,214.124..6,129.335 rows=80,626 loops=1)

  • Group Key: t0.id, t1.external_reference, (COALESCE((t3.total_duration)::double precision, (t0.length_of_whole_call * '1000'::double precision))), t5.result, t10.res0
  • Filter: bool_or((CASE WHEN ((t6.question)::text = 'Compliance'::text) THEN t6.result ELSE NULL::double precision END < t9.risk_threshold))
  • Rows Removed by Filter: 370,067
5. 924.280 5,945.129 ↓ 1.2 450,693 1

Gather Merge (cost=719,221.70..771,716.53 rows=391,242 width=163) (actual time=5,214.117..5,945.129 rows=450,693 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 181.024 5,020.849 ↑ 1.3 150,231 3 / 3

Partial GroupAggregate (cost=718,221.68..725,557.47 rows=195,621 width=163) (actual time=4,745.130..5,020.849 rows=150,231 loops=3)

  • Group Key: t0.id, t1.external_reference, (COALESCE((t3.total_duration)::double precision, (t0.length_of_whole_call * '1000'::double precision))), t5.result, t10.res0
7. 587.491 4,839.825 ↓ 2.3 450,070 3 / 3

Sort (cost=718,221.68..718,710.73 rows=195,621 width=203) (actual time=4,745.121..4,839.825 rows=450,070 loops=3)

  • Sort Key: t0.id, t1.external_reference, (COALESCE((t3.total_duration)::double precision, (t0.length_of_whole_call * '1000'::double precision))), t5.result, t10.res0
  • Sort Method: quicksort Memory: 50,492kB
8. 89.168 4,252.334 ↓ 2.3 450,070 3 / 3

Hash Left Join (cost=671,768.91..701,028.84 rows=195,621 width=203) (actual time=3,596.456..4,252.334 rows=450,070 loops=3)

  • Hash Cond: (t0.id = t10.res1)
9. 311.394 4,163.045 ↓ 2.3 450,070 3 / 3

Hash Left Join (cost=671,601.39..699,369.72 rows=195,621 width=203) (actual time=3,596.321..4,163.045 rows=450,070 loops=3)

  • Hash Cond: (t3.id = t4.enriched_transcript_id)
10. 86.115 660.650 ↑ 1.2 150,231 3 / 3

Hash Left Join (cost=36,905.53..62,014.76 rows=187,435 width=178) (actual time=403.987..660.650 rows=150,231 loops=3)

  • Hash Cond: (t2.id = t3.transcript_id)
11. 78.736 371.571 ↑ 1.2 150,231 3 / 3

Hash Left Join (cost=21,671.88..46,289.09 rows=187,435 width=174) (actual time=199.618..371.571 rows=150,231 loops=3)

  • Hash Cond: (t0.id = t2.call_id)
12. 58.579 95.027 ↑ 1.2 150,231 3 / 3

Hash Join (cost=55.38..24,201.97 rows=179,290 width=158) (actual time=0.382..95.027 rows=150,231 loops=3)

  • Hash Cond: (t0.agent_id = t1.id)
13. 36.093 36.093 ↑ 1.2 150,231 3 / 3

Parallel Seq Scan on call t0 (cost=0.00..21,681.34 rows=179,290 width=127) (actual time=0.005..36.093 rows=150,231 loops=3)

  • Filter: ((agent_id IS NOT NULL) AND (began < '2020-07-02 14:00:00+00'::timestamp with time zone) AND (began >= '2019-07-01 14:00:00+00'::timestamp with time zone))
14. 0.157 0.355 ↓ 1.0 787 3 / 3

Hash (cost=45.68..45.68 rows=776 width=47) (actual time=0.355..0.355 rows=787 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 70kB
15. 0.102 0.198 ↓ 1.0 787 3 / 3

Nested Loop (cost=0.15..45.68 rows=776 width=47) (actual time=0.019..0.198 rows=787 loops=3)

16. 0.010 0.010 ↑ 1.0 1 3 / 3

Index Scan using rule_category_pkey on rule_category t9 (cost=0.15..8.17 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=3)

  • Index Cond: (id = '2dd5c3bb-527c-4c51-b623-2a2cc1d29527'::uuid)
17. 0.086 0.086 ↓ 1.0 787 3 / 3

Seq Scan on agent t1 (cost=0.00..29.76 rows=776 width=39) (actual time=0.009..0.086 rows=787 loops=3)

18. 119.034 197.808 ↑ 1.0 449,781 3 / 3

Hash (cost=15,993.44..15,993.44 rows=449,844 width=32) (actual time=197.808..197.808 rows=449,781 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,208kB
19. 78.774 78.774 ↑ 1.0 449,781 3 / 3

Seq Scan on transcript t2 (cost=0.00..15,993.44 rows=449,844 width=32) (actual time=0.011..78.774 rows=449,781 loops=3)

20. 123.085 202.964 ↓ 1.0 449,776 3 / 3

Hash (cost=9,612.18..9,612.18 rows=449,718 width=36) (actual time=202.964..202.964 rows=449,776 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 33,964kB
21. 79.879 79.879 ↓ 1.0 449,776 3 / 3

Seq Scan on enriched_transcript t3 (cost=0.00..9,612.18 rows=449,718 width=36) (actual time=0.023..79.879 rows=449,776 loops=3)

22. 459.385 3,191.001 ↓ 2.9 1,349,277 3 / 3

Hash (cost=628,828.77..628,828.77 rows=469,367 width=57) (actual time=3,191.001..3,191.001 rows=1,349,277 loops=3)

  • Buckets: 1,048,576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 59,032kB
23. 483.716 2,731.616 ↓ 2.9 1,349,277 3 / 3

Hash Right Join (cost=344,981.51..628,828.77 rows=469,367 width=57) (actual time=1,118.509..2,731.616 rows=1,349,277 loops=3)

  • Hash Cond: (t5.scorecard_run_id = t4.id)
24. 558.541 2,048.010 ↓ 2.9 1,349,277 3 / 3

Hash Right Join (cost=328,431.70..611,046.86 rows=469,367 width=57) (actual time=917.240..2,048.010 rows=1,349,277 loops=3)

  • Hash Cond: (t6.parent = t5.id)
25. 643.037 825.585 ↓ 1.0 1,349,277 3 / 3

Bitmap Heap Scan on scorecard_result t6 (cost=41,357.31..320,571.39 rows=1,295,650 width=49) (actual time=251.942..825.585 rows=1,349,277 loops=3)

  • Recheck Cond: (((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Heap Blocks: exact=255,703
26. 0.002 182.548 ↓ 0.0 0 3 / 3

BitmapOr (cost=41,357.31..41,357.31 rows=1,336,519 width=0) (actual time=182.548..182.548 rows=0 loops=3)

27. 80.884 80.884 ↑ 1.0 449,759 3 / 3

Bitmap Index Scan on scorecard_result_parent_question_idx (cost=0.00..13,791.53 rows=456,396 width=0) (actual time=80.884..80.884 rows=449,759 loops=3)

  • Index Cond: ((question)::text = 'Communication'::text)
28. 51.412 51.412 ↑ 1.0 449,759 3 / 3

Bitmap Index Scan on scorecard_result_parent_question_idx (cost=0.00..13,717.51 rows=453,994 width=0) (actual time=51.412..51.412 rows=449,759 loops=3)

  • Index Cond: ((question)::text = 'Compliance'::text)
29. 50.250 50.250 ↓ 1.1 449,759 3 / 3

Bitmap Index Scan on scorecard_result_parent_question_idx (cost=0.00..12,876.53 rows=426,129 width=0) (actual time=50.250..50.250 rows=449,759 loops=3)

  • Index Cond: ((question)::text = 'Conduct'::text)
30. 138.511 663.884 ↑ 1.0 449,759 3 / 3

Hash (cost=281,207.30..281,207.30 rows=469,367 width=40) (actual time=663.884..663.884 rows=449,759 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 35,720kB
31. 450.352 525.373 ↑ 1.0 449,759 3 / 3

Bitmap Heap Scan on scorecard_result t5 (cost=15,714.15..281,207.30 rows=469,367 width=40) (actual time=138.344..525.373 rows=449,759 loops=3)

  • Recheck Cond: (parent IS NULL)
  • Heap Blocks: exact=243,080
32. 75.021 75.021 ↑ 1.0 449,854 3 / 3

Bitmap Index Scan on scorecard_result_unique_position (cost=0.00..15,596.81 rows=469,367 width=0) (actual time=75.021..75.021 rows=449,854 loops=3)

  • Index Cond: (parent IS NULL)
33. 119.957 199.890 ↓ 1.0 449,759 3 / 3

Hash (cost=10,928.25..10,928.25 rows=449,725 width=32) (actual time=199.890..199.890 rows=449,759 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
34. 79.933 79.933 ↓ 1.0 449,759 3 / 3

Seq Scan on scorecard_run t4 (cost=0.00..10,928.25 rows=449,725 width=32) (actual time=0.023..79.933 rows=449,759 loops=3)

35. 0.005 0.121 ↑ 11.1 18 3 / 3

Hash (cost=165.01..165.01 rows=200 width=20) (actual time=0.121..0.121 rows=18 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.004 0.116 ↑ 11.1 18 3 / 3

Subquery Scan on t10 (cost=154.51..165.01 rows=200 width=20) (actual time=0.106..0.116 rows=18 loops=3)

37. 0.006 0.112 ↑ 11.1 18 3 / 3

Unique (cost=154.51..163.01 rows=200 width=28) (actual time=0.105..0.112 rows=18 loops=3)

38. 0.045 0.106 ↑ 51.5 33 3 / 3

Sort (cost=154.51..158.76 rows=1,700 width=28) (actual time=0.104..0.106 rows=33 loops=3)

  • Sort Key: t0_1.call_id, t0_1.submitted DESC
  • Sort Method: quicksort Memory: 27kB
39. 0.020 0.061 ↑ 51.5 33 3 / 3

Hash Join (cost=31.83..63.30 rows=1,700 width=28) (actual time=0.044..0.061 rows=33 loops=3)

  • Hash Cond: (t1_1.interaction_id = t0_1.id)
40. 0.018 0.018 ↑ 51.5 33 3 / 3

Seq Scan on call_review t1_1 (cost=0.00..27.00 rows=1,700 width=20) (actual time=0.016..0.018 rows=33 loops=3)

41. 0.010 0.023 ↑ 23.7 41 3 / 3

Hash (cost=19.70..19.70 rows=970 width=40) (actual time=0.023..0.023 rows=41 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
42. 0.013 0.013 ↑ 23.7 41 3 / 3

Seq Scan on call_interaction t0_1 (cost=0.00..19.70 rows=970 width=40) (actual time=0.006..0.013 rows=41 loops=3)

Planning time : 1.737 ms
Execution time : 6,184.062 ms