explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LpoC

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 958.485 ↑ 1.0 1 1

Limit (cost=33,398.89..33,398.89 rows=1 width=36) (actual time=958.478..958.485 rows=1 loops=1)

2.          

CTE phones_with_no_commitments

3. 96.662 402.527 ↓ 0.0 0 1

Bitmap Heap Scan on all_phone_numbers (cost=8,844.90..10,197.08 rows=2,879 width=17) (actual time=402.527..402.527 rows=0 loops=1)

  • Recheck Cond: ((sending_location_id = ANY ('{39cd1fe8-408d-11ea-b4e6-0bfb129c5e83}'::uuid[])) AND (released_at IS NULL))
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 7133
  • Heap Blocks: exact=613
4. 0.742 0.742 ↓ 1.2 7,133 1

Bitmap Index Scan on active_phone_number_sending_location_idx (cost=0.00..247.60 rows=5,758 width=0) (actual time=0.742..0.742 rows=7,133 loops=1)

  • Index Cond: (sending_location_id = ANY ('{39cd1fe8-408d-11ea-b4e6-0bfb129c5e83}'::uuid[]))
5.          

SubPlan (for Bitmap Heap Scan)

6. 305.123 305.123 ↑ 1.0 83,438 1

Index Only Scan using fresh_phone_commitments_pkey on fresh_phone_commitments (cost=0.43..8,385.89 rows=84,277 width=13) (actual time=0.038..305.123 rows=83,438 loops=1)

  • Index Cond: (truncated_day = date_trunc('day'::text, timezone('Pacific/Honolulu'::text, CURRENT_TIMESTAMP)))
  • Heap Fetches: 83234
7.          

CTE phones_with_free_fresh_commitments

8. 76.565 227.986 ↓ 1.1 7,102 1

Hash Semi Join (cost=7,054.51..20,650.50 rows=6,479 width=17) (actual time=30.004..227.986 rows=7,102 loops=1)

  • Hash Cond: ((fresh_phone_commitments_1.phone_number)::text = (all_phone_numbers_1.phone_number)::text)
9. 121.703 145.431 ↓ 1.1 83,409 1

Bitmap Heap Scan on fresh_phone_commitments fresh_phone_commitments_1 (cost=5,395.72..18,717.95 rows=76,827 width=17) (actual time=23.953..145.431 rows=83,409 loops=1)

  • Recheck Cond: (truncated_day = date_trunc('day'::text, timezone('Pacific/Honolulu'::text, CURRENT_TIMESTAMP)))
  • Filter: (commitment <= 200)
  • Rows Removed by Filter: 29
  • Heap Blocks: exact=1376
10. 23.728 23.728 ↑ 1.0 83,535 1

Bitmap Index Scan on fresh_phone_commitments_pkey (cost=0.00..5,376.51 rows=84,277 width=0) (actual time=23.728..23.728 rows=83,535 loops=1)

  • Index Cond: (truncated_day = date_trunc('day'::text, timezone('Pacific/Honolulu'::text, CURRENT_TIMESTAMP)))
11. 1.955 5.990 ↓ 1.2 7,133 1

Hash (cost=1,586.82..1,586.82 rows=5,758 width=13) (actual time=5.989..5.990 rows=7,133 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 378kB
12. 3.299 4.035 ↓ 1.2 7,133 1

Bitmap Heap Scan on all_phone_numbers all_phone_numbers_1 (cost=249.04..1,586.82 rows=5,758 width=13) (actual time=0.834..4.035 rows=7,133 loops=1)

  • Recheck Cond: ((sending_location_id = ANY ('{39cd1fe8-408d-11ea-b4e6-0bfb129c5e83}'::uuid[])) AND (released_at IS NULL))
  • Heap Blocks: exact=613
13. 0.736 0.736 ↓ 1.2 7,133 1

Bitmap Index Scan on active_phone_number_sending_location_idx (cost=0.00..247.60 rows=5,758 width=0) (actual time=0.736..0.736 rows=7,133 loops=1)

  • Index Cond: (sending_location_id = ANY ('{39cd1fe8-408d-11ea-b4e6-0bfb129c5e83}'::uuid[]))
14.          

CTE phones_with_overloaded_queues

15. 0.988 297.156 ↑ 12.2 74 1

HashAggregate (cost=1,888.67..1,922.66 rows=906 width=21) (actual time=297.106..297.156 rows=74 loops=1)

  • Group Key: outbound_messages.from_number
  • Filter: (sum(outbound_messages.estimated_segments) > 6)
  • Rows Removed by Filter: 18
16. 8.821 296.168 ↑ 1.6 1,726 1

Nested Loop (cost=146.35..1,868.28 rows=2,719 width=17) (actual time=7.058..296.168 rows=1,726 loops=1)

17. 8.719 10.369 ↓ 35.5 7,102 1

HashAggregate (cost=145.78..147.78 rows=200 width=32) (actual time=6.381..10.369 rows=7,102 loops=1)

  • Group Key: (phones_with_free_fresh_commitments_1.from_number)::text
18. 1.650 1.650 ↓ 1.1 7,102 1

CTE Scan on phones_with_free_fresh_commitments phones_with_free_fresh_commitments_1 (cost=0.00..129.58 rows=6,479 width=32) (actual time=0.002..1.650 rows=7,102 loops=1)

19. 276.978 276.978 ↓ 0.0 0 7,102

Index Scan using outbound_messages_phone_number_overloaded_idx on outbound_messages (cost=0.57..8.59 rows=1 width=17) (actual time=0.039..0.039 rows=0 loops=7,102)

  • Index Cond: (((from_number)::text = (phones_with_free_fresh_commitments_1.from_number)::text) AND (processed_at > (now() - '00:01:00'::interval)))
  • Filter: (stage <> 'awaiting-number'::sms.outbound_message_stages)
20. 16.256 958.476 ↑ 4,679.0 1 1

Sort (cost=628.65..640.35 rows=4,679 width=36) (actual time=958.476..958.476 rows=1 loops=1)

  • Sort Key: all_phones.commitment
  • Sort Method: top-N heapsort Memory: 25kB
21. 2.441 942.220 ↓ 1.5 7,028 1

Subquery Scan on all_phones (cost=394.71..605.26 rows=4,679 width=36) (actual time=937.047..942.220 rows=7,028 loops=1)

  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 74
22. 7.636 642.561 ↑ 1.3 7,102 1

HashAggregate (cost=374.32..467.90 rows=9,358 width=36) (actual time=639.749..642.561 rows=7,102 loops=1)

  • Group Key: phones_with_free_fresh_commitments.commitment, phones_with_free_fresh_commitments.from_number
23. 0.850 634.925 ↑ 1.3 7,102 1

Append (cost=0.00..327.53 rows=9,358 width=36) (actual time=30.008..634.925 rows=7,102 loops=1)

24. 231.547 231.547 ↓ 1.1 7,102 1

CTE Scan on phones_with_free_fresh_commitments (cost=0.00..129.58 rows=6,479 width=36) (actual time=30.007..231.547 rows=7,102 loops=1)

25. 402.528 402.528 ↓ 0.0 0 1

CTE Scan on phones_with_no_commitments (cost=0.00..57.58 rows=2,879 width=36) (actual time=402.528..402.528 rows=0 loops=1)

26.          

SubPlan (for Subquery Scan)

27. 297.218 297.218 ↑ 12.2 74 1

CTE Scan on phones_with_overloaded_queues (cost=0.00..18.12 rows=906 width=32) (actual time=297.110..297.218 rows=74 loops=1)

Planning time : 1.316 ms
Execution time : 959.052 ms