explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MLQC

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 5,544.209 ↑ 1.0 1 1

Result (cost=3,843,913.65..3,843,913.66 rows=1 width=64) (actual time=5,544.209..5,544.209 rows=1 loops=1)

2.          

CTE __local_0__

3. 0.192 3,031.743 ↑ 1.0 10 1

Limit (cost=1,927,409.64..1,927,409.81 rows=10 width=32) (actual time=3,031.497..3,031.743 rows=10 loops=1)

4. 0.078 3,031.551 ↑ 617.0 10 1

Subquery Scan on __local_1__ (cost=1,927,409.64..1,927,517.61 rows=6,170 width=32) (actual time=3,031.496..3,031.551 rows=10 loops=1)

5. 2.717 3,031.473 ↑ 617.0 10 1

Sort (cost=1,927,409.64..1,927,425.06 rows=6,170 width=154) (actual time=3,031.471..3,031.473 rows=10 loops=1)

  • Sort Key: t0.began DESC
  • Sort Method: quicksort Memory: 1,917kB
6. 11.278 3,028.756 ↓ 1.1 6,493 1

GroupAggregate (cost=1,926,866.95..1,927,021.20 rows=6,170 width=154) (actual time=3,015.654..3,028.756 rows=6,493 loops=1)

  • Group Key: t0.id, a.external_reference, t2.total_duration
7. 21.433 3,017.478 ↓ 3.9 23,936 1

Sort (cost=1,926,866.95..1,926,882.38 rows=6,170 width=217) (actual time=3,015.645..3,017.478 rows=23,936 loops=1)

  • Sort Key: t0.id, a.external_reference, t2.total_duration
  • Sort Method: quicksort Memory: 13,405kB
8. 3.925 2,996.045 ↓ 3.9 23,936 1

Nested Loop (cost=40,763.88..1,926,478.52 rows=6,170 width=217) (actual time=2,889.976..2,996.045 rows=23,936 loops=1)

9. 127.942 2,968.184 ↓ 3.9 23,936 1

Hash Join (cost=40,763.61..1,915,465.99 rows=6,170 width=194) (actual time=2,889.949..2,968.184 rows=23,936 loops=1)

  • Hash Cond: (t3.enriched_transcript_id = t2.id)
10. 2,567.705 2,771.175 ↓ 1.0 924,535 1

Bitmap Heap Scan on sentence_vector t3 (cost=9,693.32..1,880,874.66 rows=922,492 width=119) (actual time=329.385..2,771.175 rows=924,535 loops=1)

  • Recheck Cond: (vector @@ '''payroll'''::tsquery)
  • Heap Blocks: exact=492,467
11. 203.470 203.470 ↓ 1.0 924,619 1

Bitmap Index Scan on sentence_vector_fti_vector (cost=0.00..9,462.69 rows=922,492 width=0) (actual time=203.470..203.470 rows=924,619 loops=1)

  • Index Cond: (vector @@ '''payroll'''::tsquery)
12. 7.832 69.067 ↓ 6.0 28,896 1

Hash (cost=31,010.43..31,010.43 rows=4,789 width=107) (actual time=69.067..69.067 rows=28,896 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 4,546kB
13. 3.701 61.235 ↓ 6.0 28,896 1

Gather (cost=1,146.77..31,010.43 rows=4,789 width=107) (actual time=2.454..61.235 rows=28,896 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 8.021 57.534 ↓ 4.8 9,632 3 / 3

Nested Loop (cost=146.77..29,531.53 rows=1,995 width=107) (actual time=0.713..57.534 rows=9,632 loops=3)

15. 8.211 30.248 ↓ 4.8 9,632 3 / 3

Nested Loop (cost=146.35..24,349.94 rows=2,017 width=103) (actual time=0.703..30.248 rows=9,632 loops=3)

16. 1.808 2.437 ↓ 4.9 9,800 3 / 3

Parallel Bitmap Heap Scan on call t0 (cost=145.92..11,881.78 rows=2,012 width=87) (actual time=0.691..2.437 rows=9,800 loops=3)

  • Recheck Cond: ((began >= '2020-09-06 14:00:00+00'::timestamp with time zone) AND (began < '2020-09-14 14:00:00+00'::timestamp with time zone))
  • Heap Blocks: exact=317
17. 0.629 0.629 ↓ 6.1 29,400 1 / 3

Bitmap Index Scan on call_idx_began (cost=0.00..144.72 rows=4,829 width=0) (actual time=1.888..1.888 rows=29,400 loops=1)

  • Index Cond: ((began >= '2020-09-06 14:00:00+00'::timestamp with time zone) AND (began < '2020-09-14 14:00:00+00'::timestamp with time zone))
18. 19.600 19.600 ↑ 1.0 1 29,400 / 3

Index Scan using transcript_unique_call_id on transcript t1 (cost=0.42..6.20 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=29,400)

  • Index Cond: (call_id = t0.id)
19. 19.265 19.265 ↑ 1.0 1 28,897 / 3

Index Scan using enriched_transcript_unique_transcript_id on enriched_transcript t2 (cost=0.42..2.57 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=28,897)

  • Index Cond: (transcript_id = t1.id)
20. 23.936 23.936 ↑ 1.0 1 23,936

Index Scan using agent_pkey on agent a (cost=0.28..1.79 rows=1 width=39) (actual time=0.001..0.001 rows=1 loops=23,936)

  • Index Cond: (id = t0.agent_id)
21.          

CTE __local_2__

22. 0.015 3,031.774 ↑ 1.0 1 1

Aggregate (cost=0.25..0.26 rows=1 width=32) (actual time=3,031.774..3,031.774 rows=1 loops=1)

23. 3,031.759 3,031.759 ↑ 1.0 10 1

CTE Scan on __local_0__ (cost=0.00..0.20 rows=10 width=24) (actual time=3,031.506..3,031.759 rows=10 loops=1)

24.          

Initplan (for Result)

25. 3,031.777 3,031.777 ↑ 1.0 1 1

CTE Scan on __local_2__ (cost=0.00..0.02 rows=1 width=32) (actual time=3,031.776..3,031.777 rows=1 loops=1)

26. 0.393 2,512.426 ↑ 1.0 1 1

Aggregate (cost=1,916,503.54..1,916,503.56 rows=1 width=32) (actual time=2,512.426..2,512.426 rows=1 loops=1)

27. 3.081 2,512.033 ↓ 1.1 6,493 1

Sort (cost=1,916,410.99..1,916,426.42 rows=6,170 width=154) (actual time=2,511.602..2,512.033 rows=6,493 loops=1)

  • Sort Key: t0_1.began DESC
  • Sort Method: quicksort Memory: 1,917kB
28. 4.393 2,508.952 ↓ 1.1 6,493 1

GroupAggregate (cost=1,915,899.16..1,916,022.56 rows=6,170 width=154) (actual time=2,502.997..2,508.952 rows=6,493 loops=1)

  • Group Key: t0_1.id, a_1.external_reference, t2_1.total_duration
29. 13.917 2,504.559 ↓ 3.9 23,936 1

Sort (cost=1,915,899.16..1,915,914.58 rows=6,170 width=110) (actual time=2,502.988..2,504.559 rows=23,936 loops=1)

  • Sort Key: t0_1.id, a_1.external_reference, t2_1.total_duration
  • Sort Method: quicksort Memory: 7,127kB
30. 121.814 2,490.642 ↓ 3.9 23,936 1

Hash Join (cost=40,808.34..1,915,510.72 rows=6,170 width=110) (actual time=2,467.943..2,490.642 rows=23,936 loops=1)

  • Hash Cond: (t3_1.enriched_transcript_id = t2_1.id)
31. 2,083.147 2,288.115 ↓ 1.0 924,535 1

Bitmap Heap Scan on sentence_vector t3_1 (cost=9,693.32..1,880,874.66 rows=922,492 width=16) (actual time=328.852..2,288.115 rows=924,535 loops=1)

  • Recheck Cond: (vector @@ '''payroll'''::tsquery)
  • Heap Blocks: exact=492,467
32. 204.968 204.968 ↓ 1.0 924,619 1

Bitmap Index Scan on sentence_vector_fti_vector (cost=0.00..9,462.69 rows=922,492 width=0) (actual time=204.968..204.968 rows=924,619 loops=1)

  • Index Cond: (vector @@ '''payroll'''::tsquery)
33. 11.153 80.713 ↓ 6.0 28,896 1

Hash (cost=31,055.16..31,055.16 rows=4,789 width=126) (actual time=80.713..80.713 rows=28,896 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 5,088kB
34. 2.805 69.560 ↓ 6.0 28,896 1

Gather (cost=1,186.23..31,055.16 rows=4,789 width=126) (actual time=3.583..69.560 rows=28,896 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
35. 2.917 66.755 ↓ 4.8 9,632 3 / 3

Hash Join (cost=186.23..29,576.26 rows=1,995 width=126) (actual time=1.316..66.755 rows=9,632 loops=3)

  • Hash Cond: (t0_1.agent_id = a_1.id)
36. 1.047 63.566 ↓ 4.8 9,632 3 / 3

Nested Loop (cost=146.77..29,531.53 rows=1,995 width=103) (actual time=1.005..63.566 rows=9,632 loops=3)

37. 1.356 33.622 ↓ 4.8 9,632 3 / 3

Nested Loop (cost=146.35..24,349.94 rows=2,017 width=99) (actual time=0.992..33.622 rows=9,632 loops=3)

38. 1.965 2.866 ↓ 4.9 9,800 3 / 3

Parallel Bitmap Heap Scan on call t0_1 (cost=145.92..11,881.78 rows=2,012 width=83) (actual time=0.982..2.866 rows=9,800 loops=3)

  • Recheck Cond: ((began >= '2020-09-06 14:00:00+00'::timestamp with time zone) AND (began < '2020-09-14 14:00:00+00'::timestamp with time zone))
  • Heap Blocks: exact=278
39. 0.901 0.901 ↓ 6.1 29,400 1 / 3

Bitmap Index Scan on call_idx_began (cost=0.00..144.72 rows=4,829 width=0) (actual time=2.704..2.704 rows=29,400 loops=1)

  • Index Cond: ((began >= '2020-09-06 14:00:00+00'::timestamp with time zone) AND (began < '2020-09-14 14:00:00+00'::timestamp with time zone))
40. 29.400 29.400 ↑ 1.0 1 29,400 / 3

Index Scan using transcript_unique_call_id on transcript t1_1 (cost=0.42..6.20 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=29,400)

  • Index Cond: (call_id = t0_1.id)
41. 28.897 28.897 ↑ 1.0 1 28,897 / 3

Index Scan using enriched_transcript_unique_transcript_id on enriched_transcript t2_1 (cost=0.42..2.57 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=28,897)

  • Index Cond: (transcript_id = t1_1.id)
42. 0.156 0.272 ↓ 1.0 802 3 / 3

Hash (cost=29.76..29.76 rows=776 width=39) (actual time=0.272..0.272 rows=802 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 65kB
43. 0.116 0.116 ↓ 1.0 802 3 / 3

Seq Scan on agent a_1 (cost=0.00..29.76 rows=776 width=39) (actual time=0.009..0.116 rows=802 loops=3)

Planning time : 6.242 ms
Execution time : 5,550.457 ms