explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jgpf

Settings
# exclusive inclusive rows x rows loops node
1. 0.492 2,210.199 ↓ 113.0 113 1

Sort (cost=1,807.29..1,807.30 rows=1 width=566) (actual time=2,210.157..2,210.199 rows=113 loops=1)

  • Sort Key: ""*VALUES*"".column2, (timezone(""*VALUES*"".column5, m.message_datetime))
  • Sort Method: quicksort Memory: 64kB
2. 689.141 2,209.707 ↓ 113.0 113 1

Nested Loop Left Join (cost=0.85..1,807.28 rows=1 width=566) (actual time=20.583..2,209.707 rows=113 loops=1)

  • Join Filter: ((""*VALUES*_2"".column2 = m.cid) AND (""*VALUES*_2"".column1 = m.applicant_id) AND (""*VALUES*_2"".column3 = m.property_id))
  • Rows Removed by Join Filter: 1,727,657
3. 17.804 80.946 ↓ 113.0 113 1

Nested Loop Left Join (cost=0.85..1,575.84 rows=1 width=2,305) (actual time=1.084..80.946 rows=113 loops=1)

  • Join Filter: ((""*VALUES*_1"".column2 = m.cid) AND (""*VALUES*_1"".column1 = m.customer_id) AND (""*VALUES*_1"".column4 = m.property_id))
  • Rows Removed by Join Filter: 43,936
4. 0.300 18.006 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.85..1,569.91 rows=1 width=2,237) (actual time=0.986..18.006 rows=112 loops=1)

5. 0.408 17.594 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.71..1,567.73 rows=1 width=1,391) (actual time=0.979..17.594 rows=112 loops=1)

  • Join Filter: (met.id = m.message_encoding_type_id)
6. 0.404 17.074 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.71..1,566.64 rows=1 width=1,389) (actual time=0.974..17.074 rows=112 loops=1)

7. 0.414 16.110 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.43..1,564.34 rows=1 width=1,370) (actual time=0.961..16.110 rows=112 loops=1)

  • Join Filter: (mp.id = m.message_priority_id)
8. 2.042 15.584 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.43..1,563.27 rows=1 width=1,367) (actual time=0.955..15.584 rows=112 loops=1)

  • Join Filter: (mo.id = m.message_operator_id)
  • Rows Removed by Join Filter: 4,256
9. 0.392 11.638 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.43..1,561.42 rows=1 width=1,357) (actual time=0.919..11.638 rows=112 loops=1)

  • Join Filter: (ma.id = m.message_aggregator_id)
10. 1.229 11.134 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.43..1,560.33 rows=1 width=1,352) (actual time=0.913..11.134 rows=112 loops=1)

  • Join Filter: (mt.id = m.message_type_id)
  • Rows Removed by Join Filter: 2,136
11. 0.621 8.673 ↓ 112.0 112 1

Nested Loop Left Join (cost=0.43..1,555.40 rows=1 width=789) (actual time=0.890..8.673 rows=112 loops=1)

  • Join Filter: (mst.id = m.message_status_type_id)
  • Rows Removed by Join Filter: 112
12. 1.344 7.828 ↓ 112.0 112 1

Nested Loop (cost=0.43..1,554.18 rows=1 width=323) (actual time=0.883..7.828 rows=112 loops=1)

  • Join Filter: (m.property_id = ""*VALUES*"".column1)
  • Rows Removed by Join Filter: 2,576
13. 3.460 3.460 ↓ 112.0 112 1

Index Scan using idx_messages_cid on messages m (cost=0.43..1,553.58 rows=1 width=227) (actual time=0.866..3.460 rows=112 loops=1)

  • Index Cond: (cid = 10,052)
  • Filter: (((phone_number)::text ~~ '%%'::text) AND (is_flash_sms = 0) AND (message_priority_id = ANY ('{1,2,3}'::integer[])) AND (date_trunc('DAY'::text, message_datetime) >= '2019-12-01 00:00:00-07'::timestamp with time zone) AND (date_trunc('DAY'::text, message_datetime) < '2020-09-01 00:00:00'::timestamp without time zone) AND (message_status_type_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])) AND (property_id = ANY ('{95808,95804,95800,95799,93556,93555,98344,95801,95805,95803,98342,98341,95797,95798,95810,98343,98340,95812,95806,95809,95807,95813,95802,95811}'::integer[])) AND (message_type_id = ANY ('{21,29,5,13,14,25,39,40,15,16,38,37,36,7,4,42,26,6,9,24,31,27,22,11,3,19,20,2,23,30,1,35,28,8,12,34,33,10,17,18}'::integer[])))
  • Rows Removed by Filter: 44
14. 3.024 3.024 ↑ 1.0 24 112

Values Scan on ""*VALUES*"" (cost=0.00..0.30 rows=24 width=100) (actual time=0.001..0.027 rows=24 loops=112)

15. 0.224 0.224 ↑ 5.0 2 112

Seq Scan on message_status_types mst (cost=0.00..1.10 rows=10 width=474) (actual time=0.002..0.002 rows=2 loops=112)

16. 1.232 1.232 ↑ 2.0 20 112

Seq Scan on message_types mt (cost=0.00..4.41 rows=41 width=571) (actual time=0.002..0.011 rows=20 loops=112)

17. 0.112 0.112 ↑ 4.0 1 112

Seq Scan on message_aggregators ma (cost=0.00..1.04 rows=4 width=13) (actual time=0.001..0.001 rows=1 loops=112)

18. 1.904 1.904 ↑ 1.0 38 112

Seq Scan on message_operators mo (cost=0.00..1.38 rows=38 width=18) (actual time=0.002..0.017 rows=38 loops=112)

19. 0.112 0.112 ↑ 3.0 1 112

Seq Scan on message_priorities mp (cost=0.00..1.03 rows=3 width=11) (actual time=0.001..0.001 rows=1 loops=112)

20. 0.560 0.560 ↑ 1.0 1 112

Index Scan using pk_message_originators on message_originators mog (cost=0.28..2.30 rows=1 width=27) (actual time=0.005..0.005 rows=1 loops=112)

  • Index Cond: (id = m.message_originator_id)
21. 0.112 0.112 ↑ 4.0 1 112

Seq Scan on message_encoding_types met (cost=0.00..1.04 rows=4 width=10) (actual time=0.001..0.001 rows=1 loops=112)

22. 0.112 0.112 ↓ 0.0 0 112

Index Scan using pk_message_error_types on message_error_types mert (cost=0.14..2.16 rows=1 width=854) (actual time=0.001..0.001 rows=0 loops=112)

  • Index Cond: (id = m.message_error_type_id)
23. 45.136 45.136 ↓ 196.5 393 112

Values Scan on ""*VALUES*_1"" (cost=0.00..5.90 rows=2 width=80) (actual time=0.002..0.403 rows=393 loops=112)

  • Filter: (column2 = 10,052)
24. 1,439.620 1,439.620 ↓ 201.2 15,289 113

Values Scan on ""*VALUES*_2"" (cost=0.00..229.34 rows=76 width=44) (actual time=0.001..12.740 rows=15,289 loops=113)

  • Filter: (column2 = 10,052)
Planning time : 39.791 ms