explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cww7

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 1,900.507 ↑ 1.0 1 1

Aggregate (cost=101,753.23..101,753.24 rows=1 width=8) (actual time=1,900.506..1,900.507 rows=1 loops=1)

2. 0.018 1,900.494 ↓ 7.0 7 1

Nested Loop Left Join (cost=1.28..101,753.23 rows=1 width=0) (actual time=1,331.165..1,900.494 rows=7 loops=1)

3. 0.677 1,900.378 ↓ 7.0 7 1

Nested Loop Left Join (cost=0.99..101,744.91 rows=1 width=27) (actual time=1,331.140..1,900.378 rows=7 loops=1)

  • Filter: (CASE WHEN (alternatives: SubPlan 2 or hashed SubPlan 3) THEN true WHEN (sf_opps.date_offer_sent_c IS NOT NULL) THEN true WHEN (SubPlan 7) THEN true WHEN (alternatives: SubPlan 8 or hashed SubPlan 9) THEN true WHEN ((strpos((sf_opps.stage_name)::text, 'Closed Lost'::text) > 0) AND ((strpos((sf_opps.loss_reason_c)::text, 'FW / Initialer Kundenkontakt nicht möglich'::text) > 0) OR (strpos((sf_opps.loss_reason_c)::text, 'FW / Mehrfachanfrage'::text) > 0) OR (strpos((sf_opps.loss_reason_c)::text, 'FW / Kein Kundenfeedback'::text) > 0) OR (strpos((sf_opps.loss_reason_c)::text, 'FW / Fake'::text) > 0) OR (strpos((sf_opps.loss_reason_c)::text, 'FW / Testanfrage'::text) > 0))) THEN false WHEN (NOT (SubPlan 10)) THEN false WHEN (NOT (SubPlan 11)) THEN false ELSE true END IS TRUE)
  • Rows Removed by Filter: 115
4. 7.241 68.233 ↓ 122.0 122 1

Nested Loop Left Join (cost=0.57..25,226.93 rows=1 width=99) (actual time=1.886..68.233 rows=122 loops=1)

  • Join Filter: ((sf_opps.travel_country_primary_c)::text = sf_country.name)
  • Rows Removed by Join Filter: 30134
5. 0.113 52.940 ↓ 122.0 122 1

Nested Loop Left Join (cost=0.57..25,218.35 rows=1 width=108) (actual time=1.814..52.940 rows=122 loops=1)

6. 0.257 51.729 ↓ 122.0 122 1

Nested Loop Left Join (cost=0.29..25,210.03 rows=1 width=108) (actual time=1.804..51.729 rows=122 loops=1)

7. 50.252 50.252 ↓ 122.0 122 1

Seq Scan on opportunity sf_opps (cost=0.00..25,201.72 rows=1 width=127) (actual time=1.787..50.252 rows=122 loops=1)

  • Filter: ((is_deleted IS NOT TRUE) AND (nezasa_template_id_c IS NOT NULL) AND (initial_inquiry_profile_c IS NULL) AND ((strpos((loss_reason_c)::text, 'Testanfrage'::text) IS NULL) OR (strpos((loss_reason_c)::text, 'Testanfrage'::text) = 0)) AND (CASE WHEN (date_received_c IS NOT NULL) THEN date_received_c ELSE ((created_date)::character(10))::date END = '2019-02-01'::date))
  • Rows Removed by Filter: 61788
8. 1.220 1.220 ↓ 0.0 0 122

Index Scan using net_promoter_score_c_opportunity_c_idx on net_promoter_score_c sf_nps_call (cost=0.29..8.30 rows=1 width=19) (actual time=0.010..0.010 rows=0 loops=122)

  • Index Cond: ((sf_opps.id)::text = (opportunity_c)::text)
  • Filter: ((name)::text = 'Closed opportunity'::text)
9. 1.098 1.098 ↓ 0.0 0 122

Index Scan using net_promoter_score_c_opportunity_c_idx on net_promoter_score_c sf_nps_trip (cost=0.29..8.30 rows=1 width=19) (actual time=0.009..0.009 rows=0 loops=122)

  • Index Cond: ((sf_opps.id)::text = (opportunity_c)::text)
  • Filter: ((name)::text = 'End of trip'::text)
  • Rows Removed by Filter: 0
10. 8.052 8.052 ↑ 1.0 248 122

Seq Scan on countries sf_country (cost=0.00..5.48 rows=248 width=11) (actual time=0.002..0.066 rows=248 loops=122)

11. 1.708 1.708 ↑ 1.0 1 122

Index Scan using account_pkey on account sf_acc (cost=0.41..8.43 rows=1 width=38) (actual time=0.013..0.014 rows=1 loops=122)

  • Index Cond: ((sf_opps.account_id)::text = (id)::text)
12.          

SubPlan (forNested Loop Left Join)

13. 1.220 1.220 ↓ 0.0 0 122

Index Only Scan using case_opportunity_c_idx on "case" (cost=0.29..8.32 rows=2 width=0) (actual time=0.010..0.010 rows=0 loops=122)

  • Index Cond: (opportunity_c = (sf_opps.id)::text)
  • Heap Fetches: 0
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on "case" case_1 (cost=0.00..869.11 rows=8,211 width=32) (never executed)

15. 0.117 1,824.615 ↓ 0.0 0 117

Subquery Scan on vdc (cost=76,468.36..505,408.86 rows=244,498 width=0) (actual time=15.595..15.595 rows=0 loops=117)

16. 0.103 1,824.498 ↓ 0.0 0 117

Hash Join (cost=76,468.36..502,963.88 rows=244,498 width=516) (actual time=15.594..15.594 rows=0 loops=117)

  • Hash Cond: (raw.conversation_id = customer_response.conversation_id)
17.          

CTE raw

18. 68.656 533.849 ↓ 1.0 105,457 1

Unique (cost=30,709.73..33,603.22 rows=104,851 width=289) (actual time=419.893..533.849 rows=105,457 loops=1)

19. 266.261 465.193 ↓ 1.0 105,490 1

Sort (cost=30,709.73..30,972.77 rows=105,218 width=289) (actual time=419.891..465.193 rows=105,490 loops=1)

  • Sort Key: (to_timestamp(((((conversation_messages.created_at)::double precision / '1000'::double precision))::integer)::double precision)), (reverse(split_part(reverse((conversation_messages.conversation_link)::text), '/'::text, 1))), (CASE WHEN (conversation_messages.from_name IS NULL) THEN conversation_messages.from_email ELSE conversation_messages.from_name END), (CASE WHEN (conversation_messages.to_name IS NULL) THEN conversation_messages.to_email ELSE conversation_messages.to_name END), (CASE WHEN (conversation_messages.from_email IS NULL) THEN conversation_messages.to_email ELSE conversation_messages.from_email END), conversation_messages.from_email, conversation_messages.message_type, conversation_messages.end_user_id, conversation_messages.body, conversation_messages.conversation_link
  • Sort Method: external sort Disk: 24056kB
20. 198.932 198.932 ↓ 1.0 105,490 1

Seq Scan on conversation_messages (cost=0.00..7,544.45 rows=105,218 width=289) (actual time=0.016..198.932 rows=105,490 loops=1)

  • Filter: (body IS NOT NULL)
  • Rows Removed by Filter: 27115
21.          

CTE user_account

22. 34.631 1,324.708 ↑ 21.8 4,806 1

Unique (cost=35,263.71..36,313.65 rows=104,994 width=59) (actual time=1,175.422..1,324.708 rows=4,806 loops=1)

23. 389.720 1,290.077 ↓ 1.0 105,503 1

Sort (cost=35,263.71..35,526.20 rows=104,994 width=59) (actual time=1,175.421..1,290.077 rows=105,503 loops=1)

  • Sort Key: raw_1.user_email, sf_acc_1.id, sf_acc_1.account_nr_c
  • Sort Method: external merge Disk: 5952kB
24. 173.782 900.357 ↓ 1.0 105,503 1

Hash Left Join (cost=16,054.64..22,559.24 rows=104,994 width=59) (actual time=516.591..900.357 rows=105,503 loops=1)

  • Hash Cond: (lower((raw_1.user_email)::text) = (sf_acc_1.person_email)::text)
25. 629.971 629.971 ↓ 1.0 105,457 1

CTE Scan on raw raw_1 (cost=0.00..2,097.02 rows=104,851 width=32) (actual time=419.894..629.971 rows=105,457 loops=1)

26. 31.672 96.604 ↑ 1.0 62,591 1

Hash (cost=14,657.08..14,657.08 rows=62,765 width=49) (actual time=96.604..96.604 rows=62,591 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2997kB
27. 64.932 64.932 ↓ 1.0 62,799 1

Seq Scan on account sf_acc_1 (cost=0.00..14,657.08 rows=62,765 width=49) (actual time=0.010..64.932 rows=62,799 loops=1)

  • Filter: (is_deleted IS FALSE)
  • Rows Removed by Filter: 188
28.          

CTE customer_response

29. 1.393 148.508 ↓ 2.0 1,041 1

Unique (cost=6,531.48..6,534.16 rows=533 width=32) (actual time=146.029..148.508 rows=1,041 loops=1)

30. 7.717 147.115 ↓ 8.9 4,806 1

Sort (cost=6,531.48..6,532.82 rows=537 width=32) (actual time=146.028..147.115 rows=4,806 loops=1)

  • Sort Key: (reverse(split_part(reverse((conversation_messages_1.conversation_link)::text), '/'::text, 1)))
  • Sort Method: quicksort Memory: 418kB
31. 139.398 139.398 ↓ 8.9 4,806 1

Seq Scan on conversation_messages conversation_messages_1 (cost=0.00..6,507.13 rows=537 width=32) (actual time=0.182..139.398 rows=4,806 loops=1)

  • Filter: ((body IS NOT NULL) AND ((lower((to_email)::text) ~~ '%@fwd.drifttmail.com'::text) OR (lower((to_email)::text) = 'trips@fineway.de'::text)))
  • Rows Removed by Filter: 127799
32. 88.617 1,675.089 ↑ 45,872.0 2 117

Nested Loop (cost=0.00..417,743.14 rows=91,744 width=32) (actual time=13.054..14.317 rows=2 loops=117)

  • Join Filter: ((raw.user_email)::text = (user_account.user_email)::text)
  • Rows Removed by Join Filter: 2725
33. 1,394.172 1,394.172 ↓ 0.0 0 117

CTE Scan on user_account (cost=0.00..2,362.36 rows=525 width=32) (actual time=11.906..11.916 rows=0 loops=117)

  • Filter: ((sf_account_id)::text = (sf_acc.id)::text)
  • Rows Removed by Filter: 4748
34. 192.300 192.300 ↓ 2.3 79,740 4

CTE Scan on raw (cost=0.00..2,883.40 rows=34,950 width=64) (actual time=0.013..48.075 rows=79,740 loops=4)

  • Filter: ((created_at)::date >= (sf_opps.created_date)::date)
35. 0.322 149.306 ↓ 2.0 1,041 1

Hash (cost=10.66..10.66 rows=533 width=32) (actual time=149.306..149.306 rows=1,041 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 59kB
36. 148.984 148.984 ↓ 2.0 1,041 1

CTE Scan on customer_response (cost=0.00..10.66 rows=533 width=32) (actual time=146.031..148.984 rows=1,041 loops=1)

37. 0.460 2.300 ↓ 0.0 0 115

Bitmap Heap Scan on opportunity_history a (cost=4.46..24.20 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=115)

  • Recheck Cond: ((opportunity_id)::text = (sf_opps.id)::text)
  • Filter: ((stage_name)::text = 'Offer erstellen'::text)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=230
38. 1.840 1.840 ↑ 2.5 2 115

Bitmap Index Scan on opp_id_idx (cost=0.00..4.46 rows=5 width=0) (actual time=0.016..0.016 rows=2 loops=115)

  • Index Cond: ((opportunity_id)::text = (sf_opps.id)::text)
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on opportunity_history a_1 (cost=0.00..13,306.35 rows=21,469 width=32) (never executed)

  • Filter: ((stage_name)::text = 'Offer erstellen'::text)
40. 1.610 1.610 ↓ 0.0 0 115

Index Scan using opp_id_idx on opportunity_history a_2 (cost=0.42..24.52 rows=3 width=0) (actual time=0.014..0.014 rows=0 loops=115)

  • Index Cond: ((opportunity_id)::text = (sf_opps.id)::text)
  • Filter: ((stage_name)::text <> ALL ('{Neu,"Erstkontakt (SPARK)"}'::text[]))
  • Rows Removed by Filter: 2
41. 0.015 0.015 ↓ 0.0 0 1

Index Only Scan using task_what_id_idx on task a_3 (cost=0.42..8.49 rows=4 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (what_id = (sf_opps.id)::text)
  • Heap Fetches: 0
42. 0.098 0.098 ↑ 1.0 1 7

Index Scan using opp_nr_idx on opportunity sf_opps_1 (cost=0.29..8.31 rows=1 width=27) (actual time=0.013..0.014 rows=1 loops=7)

  • Index Cond: ((opportunity_nr_c)::text = (sf_opps.opportunity_nr_c)::text)
  • Filter: ((account_id)::text = (sf_opps.account_id)::text)