explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cpEu

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 8,346.062 ↑ 1.0 1 1

Aggregate (cost=102,021.77..102,021.78 rows=1 width=8) (actual time=8,346.060..8,346.062 rows=1 loops=1)

2. 0.032 8,346.044 ↓ 7.0 7 1

Nested Loop Left Join (cost=1.28..102,021.77 rows=1 width=0) (actual time=6,999.885..8,346.044 rows=7 loops=1)

3. 1.112 8,345.865 ↓ 7.0 7 1

Nested Loop Left Join (cost=0.99..102,013.45 rows=1 width=27) (actual time=6,999.856..8,345.865 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. 21.980 236.878 ↓ 122.0 122 1

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

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

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

6. 0.374 191.058 ↓ 122.0 122 1

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

7. 184.462 184.462 ↓ 122.0 122 1

Seq Scan on opportunity sf_opps (cost=0.00..25,201.72 rows=1 width=127) (actual time=15.645..184.462 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. 6.222 6.222 ↓ 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.051..0.051 rows=0 loops=122)

  • Index Cond: ((sf_opps.id)::text = (opportunity_c)::text)
  • Filter: ((name)::text = 'Closed opportunity'::text)
9. 1.952 1.952 ↓ 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.016..0.016 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. 21.594 21.594 ↑ 1.0 248 122

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

11. 3.294 3.294 ↑ 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.026..0.027 rows=1 loops=122)

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

SubPlan (forNested Loop Left Join)

13. 2.318 2.318 ↓ 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.019..0.019 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 8,094.294 ↓ 0.0 0 117

Subquery Scan on vdc (cost=76,738.55..829,919.48 rows=7,456,275 width=0) (actual time=69.182..69.182 rows=0 loops=117)

16. 0.366 8,094.177 ↓ 0.0 0 117

Hash Join (cost=76,738.55..755,356.73 rows=7,456,275 width=516) (actual time=69.181..69.181 rows=0 loops=117)

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

CTE raw

18. 264.343 3,377.054 ↓ 1.0 105,433 1

Unique (cost=30,683.54..33,574.59 rows=104,763 width=289) (actual time=2,947.941..3,377.054 rows=105,433 loops=1)

19. 1,612.938 3,112.711 ↓ 1.0 105,466 1

Sort (cost=30,683.54..30,946.36 rows=105,129 width=289) (actual time=2,947.939..3,112.711 rows=105,466 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: 24048kB
20. 1,499.773 1,499.773 ↓ 1.0 105,466 1

Seq Scan on conversation_messages (cost=0.00..7,540.33 rows=105,129 width=289) (actual time=0.042..1,499.773 rows=105,466 loops=1)

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

CTE user_account

22. 212.930 6,972.669 ↑ 21.8 4,805 1

Unique (cost=35,248.50..36,297.56 rows=104,906 width=59) (actual time=6,270.662..6,972.669 rows=4,805 loops=1)

23. 1,831.074 6,759.739 ↓ 1.0 105,479 1

Sort (cost=35,248.50..35,510.76 rows=104,906 width=59) (actual time=6,270.661..6,759.739 rows=105,479 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. 647.685 4,928.665 ↓ 1.0 105,479 1

Hash Left Join (cost=16,054.64..22,555.50 rows=104,906 width=59) (actual time=3,458.229..4,928.665 rows=105,479 loops=1)

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

CTE Scan on raw raw_1 (cost=0.00..2,095.26 rows=104,763 width=32) (actual time=2,947.944..3,771.046 rows=105,433 loops=1)

26. 138.734 509.934 ↑ 1.0 62,591 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2997kB
27. 371.200 371.200 ↓ 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.020..371.200 rows=62,799 loops=1)

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

CTE customer_response

29. 7.925 179.550 ↑ 15.6 1,041 1

HashAggregate (cost=6,053.01..6,337.70 rows=16,268 width=32) (actual time=178.976..179.550 rows=1,041 loops=1)

  • Group Key: reverse(split_part(reverse((conversation_messages_1.conversation_link)::text), '/'::text, 1))
30. 171.625 171.625 ↑ 4.5 4,806 1

Seq Scan on conversation_messages conversation_messages_1 (cost=0.00..5,999.10 rows=21,561 width=32) (actual time=0.133..171.625 rows=4,806 loops=1)

  • Filter: ((body IS NOT NULL) AND (((to_email)::text ~~ '%@fwd.drifttmail.com'::text) OR ((to_email)::text = 'trips@fineway.de'::text)))
  • Rows Removed by Filter: 127767
31. 211.724 7,912.593 ↑ 45,834.0 2 117

Nested Loop (cost=0.00..417,396.47 rows=91,668 width=32) (actual time=64.249..67.629 rows=2 loops=117)

  • Join Filter: ((raw.user_email)::text = (user_account.user_email)::text)
  • Rows Removed by Join Filter: 2724
32. 7,166.601 7,166.601 ↓ 0.0 0 117

CTE Scan on user_account (cost=0.00..2,360.38 rows=525 width=32) (actual time=61.232..61.253 rows=0 loops=117)

  • Filter: ((sf_account_id)::text = (sf_acc.id)::text)
  • Rows Removed by Filter: 4747
33. 534.268 534.268 ↓ 2.3 79,728 4

CTE Scan on raw (cost=0.00..2,880.98 rows=34,921 width=64) (actual time=0.021..133.567 rows=79,728 loops=4)

  • Filter: ((created_at)::date >= (sf_opps.created_date)::date)
34. 0.594 181.218 ↑ 15.6 1,041 1

Hash (cost=325.36..325.36 rows=16,268 width=32) (actual time=181.218..181.218 rows=1,041 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 171kB
35. 180.624 180.624 ↑ 15.6 1,041 1

CTE Scan on customer_response (cost=0.00..325.36 rows=16,268 width=32) (actual time=178.980..180.624 rows=1,041 loops=1)

36. 1.495 5.060 ↓ 0.0 0 115

Bitmap Heap Scan on opportunity_history a (cost=4.46..24.20 rows=1 width=0) (actual time=0.044..0.044 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
37. 3.565 3.565 ↑ 2.5 2 115

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

  • Index Cond: ((opportunity_id)::text = (sf_opps.id)::text)
38. 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)
39. 2.875 2.875 ↓ 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.025..0.025 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
40. 0.034 0.034 ↓ 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.034..0.034 rows=0 loops=1)

  • Index Cond: (what_id = (sf_opps.id)::text)
  • Heap Fetches: 0
41. 0.147 0.147 ↑ 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.020..0.021 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)
Planning time : 93.765 ms
Execution time : 8,375.546 ms