explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UMIt

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 7,597.321 ↑ 1.0 100 1

Limit (cost=6,734,575.85..6,734,581.10 rows=100 width=661) (actual time=7,596.766..7,597.321 rows=100 loops=1)

2. 0.324 7,597.215 ↑ 43,284.3 100 1

Unique (cost=6,734,575.85..6,918,534.25 rows=4,328,433 width=661) (actual time=7,596.764..7,597.215 rows=100 loops=1)

3.          

CTE conversation_messages

4. 315.764 2,219.311 ↑ 2.3 158,351 1

WindowAgg (cost=195,109.76..217,440.20 rows=357,287 width=303) (actual time=1,704.115..2,219.311 rows=158,351 loops=1)

5. 816.838 1,903.547 ↑ 2.3 158,351 1

Sort (cost=195,109.76..196,002.98 rows=357,287 width=267) (actual time=1,704.078..1,903.547 rows=158,351 loops=1)

  • Sort Key: (reverse(split_part(reverse((conversation_messages.conversation_link)::text), '/'::text, 1)))
  • Sort Method: external merge Disk: 35,968kB
6. 1,086.709 1,086.709 ↑ 2.3 158,351 1

Seq Scan on conversation_messages (cost=0.00..71,785.89 rows=357,287 width=267) (actual time=724.631..1,086.709 rows=158,351 loops=1)

  • Filter: ((body IS NOT NULL) AND ((message_type)::text = 'CHAT'::text) AND CASE WHEN (created_at IS NULL) THEN ((created_at_timestamp)::date >= (('now'::cstring)::date - '60 days'::interval)) ELSE ((to_timestamp(((((created_at)::double precision / '1000'::double precision))::integer)::double precision))::date >= (('now'::cstring)::date - '60 days'::interval)) END)
  • Rows Removed by Filter: 683,744
7.          

CTE customer_response

8. 6.702 1,188.176 ↓ 1.2 2,457 1

Unique (cost=75,549.03..75,559.15 rows=2,007 width=32) (actual time=1,175.670..1,188.176 rows=2,457 loops=1)

9. 39.844 1,181.474 ↓ 12.8 25,991 1

Sort (cost=75,549.03..75,554.09 rows=2,024 width=32) (actual time=1,175.668..1,181.474 rows=25,991 loops=1)

  • Sort Key: (CASE WHEN (conversation_messages_1.conversation_id IS NOT NULL) THEN (conversation_messages_1.conversation_id)::text ELSE reverse(split_part(reverse((conversation_messages_1.conversation_link)::text), '/'::text, 1)) END)
  • Sort Method: quicksort Memory: 1,987kB
10. 1,141.630 1,141.630 ↓ 12.8 25,991 1

Seq Scan on conversation_messages conversation_messages_1 (cost=0.00..75,437.88 rows=2,024 width=32) (actual time=907.094..1,141.630 rows=25,991 loops=1)

  • Filter: ((conversation_id IS NOT NULL) OR (((lower((to_email)::text) ~~ '%@fwd.drifttmail.com'::text) OR (lower((to_email)::text) = 'trips@fineway.de'::text)) AND (body IS NOT NULL) AND (((created_at_timestamp)::date >= (('now'::cstring)::date - '60 days'::interval)) OR ((to_timestamp(((((created_at)::double precision / '1000'::double precision))::integer)::double precision))::date >= (('now'::cstring)::date - '60 days'::interval)))))
  • Rows Removed by Filter: 816,104
11.          

CTE raw

12. 1,850.405 6,712.110 ↑ 64.5 55,746 1

Hash Join (cost=30,349.55..308,817.79 rows=3,598,007 width=331) (actual time=3,298.155..6,712.110 rows=55,746 loops=1)

  • Hash Cond: (conversation_messages_2.conversation_id = customer_response.conversation_id)
13. 334.570 3,671.688 ↑ 2.3 158,504 1

Hash Left Join (cost=30,284.32..118,826.30 rows=358,546 width=352) (actual time=2,092.258..3,671.688 rows=158,504 loops=1)

  • Hash Cond: (conversation_messages_2.conversation_id = (dc_1.conversation_id)::text)
14. 437.343 3,126.633 ↑ 2.3 158,416 1

Hash Left Join (cost=17,364.24..64,785.95 rows=358,123 width=299) (actual time=1,879.214..3,126.633 rows=158,416 loops=1)

  • Hash Cond: (lower((conversation_messages_2.user_email)::text) = (sf_acc.person_email)::text)
15. 2,515.362 2,515.362 ↑ 2.3 158,351 1

CTE Scan on conversation_messages conversation_messages_2 (cost=0.00..7,145.74 rows=357,287 width=304) (actual time=1,704.119..2,515.362 rows=158,351 loops=1)

16. 56.316 173.928 ↑ 1.0 106,415 1

Hash (cost=15,095.24..15,095.24 rows=106,560 width=48) (actual time=173.928..173.928 rows=106,415 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,608kB
17. 117.612 117.612 ↓ 1.0 106,643 1

Seq Scan on account sf_acc (cost=0.00..15,095.24 rows=106,560 width=48) (actual time=0.020..117.612 rows=106,643 loops=1)

  • Filter: (is_deleted IS FALSE)
  • Rows Removed by Filter: 190
18. 118.448 210.485 ↑ 1.0 199,031 1

Hash (cost=8,286.37..8,286.37 rows=199,337 width=63) (actual time=210.485..210.485 rows=199,031 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,839kB
19. 92.037 92.037 ↑ 1.0 199,031 1

Seq Scan on conversations dc_1 (cost=0.00..8,286.37 rows=199,337 width=63) (actual time=0.010..92.037 rows=199,031 loops=1)

20. 0.742 1,190.017 ↓ 1.2 2,457 1

Hash (cost=40.14..40.14 rows=2,007 width=32) (actual time=1,190.016..1,190.017 rows=2,457 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 135kB
21. 1,189.275 1,189.275 ↓ 1.2 2,457 1

CTE Scan on customer_response (cost=0.00..40.14 rows=2,007 width=32) (actual time=1,175.671..1,189.275 rows=2,457 loops=1)

22. 227.810 7,596.891 ↑ 20,226.3 214 1

Sort (cost=6,132,758.71..6,143,579.80 rows=4,328,433 width=661) (actual time=7,596.763..7,596.891 rows=214 loops=1)

  • Sort Key: raw.created_at, raw.conversation_started_at, raw.conversation_id, raw.transaction_id, raw.sf_account_id, raw.sf_account_nr, raw.conv_from_raw, raw.conv_from, raw.conv_to, raw.message_type, raw.end_user_id, raw.body, raw.conversation_link, sf_opp.travel_country_primary_c, sf_opp.nezasa_template_id_c, dc.tags
  • Sort Method: external merge Disk: 27,760kB
23. 64.681 7,369.081 ↑ 65.4 66,180 1

Hash Left Join (cost=41,509.42..1,838,677.74 rows=4,328,433 width=661) (actual time=3,650.659..7,369.081 rows=66,180 loops=1)

  • Hash Cond: (raw.conversation_id = (dc.conversation_id)::text)
24. 127.281 7,283.783 ↑ 67.0 55,886 1

Hash Left Join (cost=39,611.00..1,756,076.87 rows=3,741,811 width=574) (actual time=3,629.981..7,283.783 rows=55,886 loops=1)

  • Hash Cond: (raw.transaction_id = (sf_opp.dps_lead_profile_c)::text)
25. 6,832.243 6,832.243 ↑ 64.5 55,746 1

CTE Scan on raw (cost=0.00..71,960.14 rows=3,598,007 width=556) (actual time=3,298.158..6,832.243 rows=55,746 loops=1)

26. 65.201 324.259 ↑ 1.6 78,565 1

Hash (cost=36,887.11..36,887.11 rows=122,311 width=55) (actual time=324.258..324.259 rows=78,565 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,234kB
27. 259.058 259.058 ↑ 1.0 122,197 1

Seq Scan on opportunity sf_opp (cost=0.00..36,887.11 rows=122,311 width=55) (actual time=0.014..259.058 rows=122,197 loops=1)

28. 5.849 20.617 ↑ 1.0 10,330 1

Hash (cost=1,769.30..1,769.30 rows=10,330 width=97) (actual time=20.617..20.617 rows=10,330 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,410kB
29. 14.768 14.768 ↑ 1.0 10,330 1

Seq Scan on conversation_closed dc (cost=0.00..1,769.30 rows=10,330 width=97) (actual time=0.026..14.768 rows=10,330 loops=1)

Planning time : 2.776 ms
Execution time : 7,625.452 ms