explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FJxU

Settings
# exclusive inclusive rows x rows loops node
1. 1,151,135.634 1,151,135.634 ↓ 9,243.0 9,243 1

CTE Scan on recs_distinct (cost=23,588.68..23,588.72 rows=1 width=484) (actual time=1,151,022.014..1,151,135.634 rows=9,243 loops=1)

2.          

CTE prospects

3. 216.947 222.891 ↓ 1.0 2,099 1

Hash Left Join (cost=2.53..789.28 rows=2,090 width=139) (actual time=0.796..222.891 rows=2,099 loops=1)

  • Hash Cond: (p.customer_type = rels.opp_relationship_code)
4. 1.819 5.937 ↓ 1.0 2,099 1

Hash Left Join (cost=1.82..235.06 rows=2,090 width=88) (actual time=0.065..5.937 rows=2,099 loops=1)

  • Hash Cond: (p.brand_pk = sp.brand_pk)
5. 4.091 4.091 ↓ 1.0 2,099 1

Seq Scan on co_prospect p (cost=0.00..227.50 rows=2,090 width=77) (actual time=0.023..4.091 rows=2,099 loops=1)

  • Filter: (prospect_creation_time >= '2015-01-01'::date)
  • Rows Removed by Filter: 1605
6. 0.018 0.027 ↑ 1.0 54 1

Hash (cost=1.14..1.14 rows=54 width=19) (actual time=0.026..0.027 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
7. 0.009 0.009 ↑ 1.0 54 1

Seq Scan on co_supplier_brands sp (cost=0.00..1.14 rows=54 width=19) (actual time=0.003..0.009 rows=54 loops=1)

8. 0.004 0.007 ↑ 1.0 5 1

Hash (cost=0.65..0.65 rows=5 width=45) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on co_prospect_opp_relationship rels (cost=0.00..0.65 rows=5 width=45) (actual time=0.002..0.003 rows=5 loops=1)

10.          

CTE data

11. 159.865 958,601.848 ↓ 51,143.7 153,431 1

Merge Join (cost=16,763.01..22,799.14 rows=3 width=534) (actual time=412.871..958,601.848 rows=153,431 loops=1)

  • Merge Cond: (meta_stage.wf_node_pk = n.wf_node_pk)
12. 147.049 958,441.931 ↓ 38,357.8 153,431 1

Nested Loop Left Join (cost=16,761.09..40,904.85 rows=4 width=463) (actual time=412.821..958,441.931 rows=153,431 loops=1)

13. 77.161 958,294.882 ↓ 38,357.8 153,431 1

Nested Loop Left Join (cost=16,760.82..40,903.67 rows=4 width=455) (actual time=412.807..958,294.882 rows=153,431 loops=1)

14. 309.641 958,064.290 ↓ 38,357.8 153,431 1

Nested Loop (cost=16,760.67..40,903.03 rows=4 width=444) (actual time=412.798..958,064.290 rows=153,431 loops=1)

  • -> Index Scan using wf_workflow_instance_log_type_pkey on wf_workflow_instance_log_type (cost=0.13..0.15 rows=1 width=12) (actual time
15. 957,328.616 957,754.649 ↓ 38,357.8 153,431 1

Nested Loop (cost=16,760.54..40,902.43 rows=4 width=440) (actual time=412.781..957,754.649 rows=153,431 loops=1)

  • Join Filter: (p_1.wf_instance_pk = wf_workflow_instance_log.wf_instance_pk)
  • Rows Removed by Join Filter: 8089397241
  • -> Seq Scan on wf_workflow_instance_log (cost=0.00..13164.28 rows=874168 width=24) (actual time=0.002..47.464 rows=874168 loops=
  • Index Cond: (wf_log_entry_type_pk = wf_workflow_instance_log.wf_log_entry_type_pk)
16. 63.134 426.033 ↓ 9,254.0 9,254 1

Nested Loop (cost=16,760.54..16,811.05 rows=1 width=428) (actual time=322.106..426.033 rows=9,254 loops=1)

  • -> Index Only Scan using wf_state_pkey on wf_state wf_step_state (cost=0.13..0.15 rows=1 width=4) (actual time=0.003..0.00
17. 26.566 362.899 ↓ 9,254.0 9,254 1

Merge Join (cost=16,760.41..16,810.89 rows=1 width=432) (actual time=322.066..362.899 rows=9,254 loops=1)

  • Merge Cond: (ni.wf_node_pk = meta_stage.wf_node_pk)
  • Index Cond: (wf_state_pk = ni.wf_node_state_pk)
  • Heap Fetches: 0
18. 12.292 283.015 ↑ 1.0 9,843 1

Sort (cost=4,518.25..4,543.72 rows=10,188 width=425) (actual time=276.492..283.015 rows=9,843 loops=1)

  • Sort Key: ni.wf_node_pk
  • Sort Method: quicksort Memory: 2999kB
19. 29.673 270.723 ↑ 1.0 9,843 1

Nested Loop (cost=1.53..3,840.00 rows=10,188 width=425) (actual time=1.253..270.723 rows=9,843 loops=1)

  • Join Filter: (p_1.wf_instance_pk = ni.wf_instance_pk)
  • -> Index Scan using wf_node_instance_idx1 on wf_node_instance ni (cost=0.42..0.66 rows=6 width=48) (actu
20. 241.045 241.050 ↑ 1.0 1,531 1

Hash Join (cost=1.10..2,693.33 rows=1,568 width=377) (actual time=1.227..241.050 rows=1,531 loops=1)

  • Hash Cond: (wi.wf_state_pk = wf_instance_state.wf_state_pk)
  • -> Nested Loop (cost=0.42..2681.35 rows=2090 width=374) (actual time=1.215..240.096 rows=2007 loop
  • -> CTE Scan on prospects p_1 (cost=0.00..41.80 rows=2090 width=366) (actual time=0.798..225.
  • -> Index Scan using wf_workflow_instance_pkey on wf_workflow_instance wi (cost=0.42..1.26 ro
  • Index Cond: (wf_instance_pk = p_1.wf_instance_pk)
  • Index Cond: (wf_instance_pk = wi.wf_instance_pk)
21. 0.005 0.005 ↑ 1.0 3 1

Hash (cost=0.65..0.65 rows=3 width=11) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on wf_state wf_instance_state (cost=0.00..0.65 rows=3 width=11) (actual time=0.0
  • Filter: (wf_state_code <> 'die'::text)
  • Rows Removed by Filter: 1
22. 53.318 53.318 ↓ 249.9 9,248 1

Sort (cost=12,241.69..12,241.78 rows=37 width=7) (actual time=45.567..53.318 rows=9,248 loops=1)

  • Sort Key: meta_stage.wf_node_pk
  • Sort Method: quicksort Memory: 26kB
  • -> Seq Scan on wf_instance_metadata meta_stage (cost=0.00..12240.72 rows=37 width=7) (actual time=1.725..45.55
  • Filter: (wf_metadata_pk = 8)
  • Rows Removed by Filter: 746098
23. 153.431 153.431 ↑ 1.0 1 153,431

Index Scan using groups_pk on groups (cost=0.14..0.16 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=153,431)

  • Index Cond: (group_id = ni.owner_group_id)
24. 0.000 0.000 ↓ 0.0 0 153,431

Index Scan using persons_pk on persons p1 (cost=0.28..0.30 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=153,431)

  • Index Cond: (person_id = ni.owner_user_id)
25. 0.028 0.052 ↑ 1.0 30 1

Sort (cost=1.91..1.99 rows=30 width=30) (actual time=0.038..0.052 rows=30 loops=1)

  • Sort Key: n.wf_node_pk
  • Sort Method: quicksort Memory: 27kB
26. 0.024 0.024 ↑ 1.0 30 1

Seq Scan on wf_node n (cost=0.00..1.18 rows=30 width=30) (actual time=0.015..0.024 rows=30 loops=1)

  • Filter: (wf_pk = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 16
27.          

CTE stage_complete

28. 70.757 959,025.718 ↓ 5,050.0 5,050 1

HashAggregate (cost=0.08..0.09 rows=1 width=40) (actual time=959,015.721..959,025.718 rows=5,050 loops=1)

  • Group Key: d.prospect_pk, d.wf_instance_pk, d.stage
29. 958,954.961 958,954.961 ↓ 80,765.0 80,765 1

CTE Scan on data d (cost=0.00..0.08 rows=1 width=40) (actual time=412.883..958,954.961 rows=80,765 loops=1)

  • Filter: ((start_wf_node_instance_pk IS NOT NULL) AND (end_wf_node_instance_pk <> wf_node_instance_pk) AND (wf_log_entry_code = 'transition'::text))
  • Rows Removed by Filter: 72666
30.          

CTE recs_distinct

31. 22.281 1,151,116.067 ↓ 9,243.0 9,243 1

Unique (cost=0.15..0.16 rows=1 width=482) (actual time=1,151,021.372..1,151,116.067 rows=9,243 loops=1)

32. 288.896 1,151,093.786 ↓ 153,428.0 153,428 1

Sort (cost=0.15..0.15 rows=1 width=482) (actual time=1,151,021.370..1,151,093.786 rows=153,428 loops=1)

  • Sort Key: d_1.prospect_num, d_1.prospect_name, d_1.customer_type, d_1.prospect_created, d_1.node_name
  • Sort Method: external merge Disk: 25800kB
33. 76,927.144 1,150,804.890 ↓ 153,428.0 153,428 1

Nested Loop (cost=0.00..0.14 rows=1 width=482) (actual time=959,044.351..1,150,804.890 rows=153,428 loops=1)

  • Join Filter: ((d_1.prospect_pk = ss.prospect_pk) AND (d_1.wf_instance_pk = ss.wf_instance_pk) AND (d_1.stage = ss.stage))
  • Rows Removed by Join Filter: 774673122
34. 959,035.696 959,035.696 ↓ 5,050.0 5,050 1

CTE Scan on stage_complete ss (cost=0.00..0.02 rows=1 width=40) (actual time=959,015.723..959,035.696 rows=5,050 loops=1)

35. 114,842.050 114,842.050 ↓ 51,143.7 153,431 5,050

CTE Scan on data d_1 (cost=0.00..0.06 rows=3 width=1,010) (actual time=0.003..22.741 rows=153,431 loops=5,050)