explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ivaK

Settings
# exclusive inclusive rows x rows loops node
1. 2,676.982 2,676.982 ↓ 9,282.0 9,282 1

CTE Scan on recs_distinct (cost=36,599.24..36,599.28 rows=1 width=484) (actual time=2,539.867..2,676.982 rows=9,282 loops=1)

  • (actual time=0.008..0.015 rows=6 loops=1532)
  • Planning time: 4.228 ms
  • Execution time: 2693.410 ms
2.          

CTE prospects

3. 269.842 277.270 ↓ 1.0 2,113 1

Hash Left Join (cost=2.53..865.08 rows=2,099 width=139) (actual time=0.326..277.270 rows=2,113 loops=1)

  • Hash Cond: (p.customer_type = rels.opp_relationship_code)
4. 2.824 7.420 ↓ 1.0 2,113 1

Hash Left Join (cost=1.82..289.88 rows=2,099 width=88) (actual time=0.058..7.420 rows=2,113 loops=1)

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

Seq Scan on co_prospect p (cost=0.00..267.88 rows=2,099 width=77) (actual time=0.015..4.560 rows=2,113 loops=1)

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

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

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

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

8. 0.005 0.008 ↑ 1.0 5 1

Hash (cost=0.65..0.65 rows=5 width=45) (actual time=0.008..0.008 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.001..0.003 rows=5 loops=1)

10.          

CTE data

11. 120.340 1,445.279 ↓ 902.2 154,280 1

Nested Loop Left Join (cost=16,935.65..35,724.44 rows=171 width=534) (actual time=519.987..1,445.279 rows=154,280 loops=1)

12. 81.588 1,170.659 ↓ 902.2 154,280 1

Hash Left Join (cost=16,935.37..35,670.14 rows=171 width=473) (actual time=519.968..1,170.659 rows=154,280 loops=1)

  • Hash Cond: (ni.owner_group_id = groups.group_id)
13. 82.265 1,089.012 ↓ 902.2 154,280 1

Hash Join (cost=16,929.66..35,663.72 rows=171 width=462) (actual time=519.900..1,089.012 rows=154,280 loops=1)

  • Hash Cond: (meta_stage.wf_node_pk = n.wf_node_pk)
14. 83.634 1,006.712 ↓ 588.9 154,280 1

Hash Join (cost=16,928.11..35,661.18 rows=262 width=444) (actual time=519.856..1,006.712 rows=154,280 loops=1)

  • Hash Cond: (wf_workflow_instance_log.wf_log_entry_type_pk = wf_workflow_instance_log_type.wf_log_entry_type_pk)
15. 487.685 923.069 ↓ 588.9 154,280 1

Hash Join (cost=16,927.33..35,656.79 rows=262 width=440) (actual time=519.836..923.069 rows=154,280 loops=1)

  • Hash Cond: (wf_workflow_instance_log.wf_instance_pk = p_1.wf_instance_pk)
  • -> Seq Scan on wf_workflow_instance_log (cost=0.00..13251.78 rows=876018 width=24) (actual time=0.003..179.022 rows=877951 loops
16. 5.765 435.384 ↓ 290.4 9,292 1

Hash (cost=16,926.93..16,926.93 rows=32 width=428) (actual time=435.384..435.384 rows=9,292 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2114kB
17. 12.873 429.619 ↓ 290.4 9,292 1

Nested Loop (cost=16,872.17..16,926.93 rows=32 width=428) (actual time=394.217..429.619 rows=9,292 loops=1)

  • Join Filter: (ni.wf_node_state_pk = wf_step_state.wf_state_pk)
  • Rows Removed by Join Filter: 27876
18. 7.616 407.454 ↓ 290.4 9,292 1

Merge Join (cost=16,872.17..16,924.36 rows=32 width=432) (actual time=394.206..407.454 rows=9,292 loops=1)

  • Merge Cond: (ni.wf_node_pk = meta_stage.wf_node_pk)
19. 12.694 337.062 ↑ 1.1 9,883 1

Sort (cost=4,452.68..4,478.83 rows=10,460 width=425) (actual time=333.452..337.062 rows=9,883 loops=1)

  • Sort Key: ni.wf_node_pk
  • Sort Method: quicksort Memory: 3010kB
20. 324.361 324.368 ↑ 1.1 9,883 1

Nested Loop (cost=1.41..3,754.34 rows=10,460 width=425) (actual time=0.765..324.368 rows=9,883 loops=1)

  • Join Filter: (p_1.wf_instance_pk = ni.wf_instance_pk)
  • -> Hash Join (cost=0.98..2498.36 rows=1574 width=377) (actual time=0.749..294.578 rows=1532 loops=
  • Hash Cond: (wi.wf_state_pk = wf_instance_state.wf_state_pk)
  • -> Nested Loop (cost=0.29..2474.06 rows=2099 width=374) (actual time=0.739..293.013 rows=202
  • -> CTE Scan on prospects p_1 (cost=0.00..41.98 rows=2099 width=366) (actual time=0.330
  • -> Index Scan using wf_workflow_instance_pkey on wf_workflow_instance wi (cost=0.29..1
  • Index Cond: (wf_instance_pk = p_1.wf_instance_pk)
21. 0.007 0.007 ↑ 1.0 3 1

Hash (cost=0.65..0.65 rows=3 width=11) (actual time=0.007..0.007 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 ti
  • Filter: (wf_state_code <> 'die'::text)
  • Rows Removed by Filter: 1
22. 0.000 0.000 ↓ 0.0

Index Scan using wf_node_instance_idx1 on wf_node_instance ni (cost=0.42..0.71 rows=7 width=48) (actual rows= loops=)

  • Index Cond: (wf_instance_pk = wi.wf_instance_pk)
23. 62.776 62.776 ↓ 4.0 9,284 1

Sort (cost=12,419.05..12,424.89 rows=2,338 width=7) (actual time=60.747..62.776 rows=9,284 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..12288.22 rows=2338 width=7) (actual time=1.95
  • Filter: (wf_metadata_pk = 8)
  • Rows Removed by Filter: 748861
24. 9.292 9.292 ↑ 1.0 4 9,292

Materialize (cost=0.00..0.66 rows=4 width=4) (actual time=0.000..0.001 rows=4 loops=9,292)

  • -> Seq Scan on wf_state wf_step_state (cost=0.00..0.64 rows=4 width=4) (actual time=0.004..0.004 rows=4 loops=
25. 0.007 0.009 ↑ 1.0 8 1

Hash (cost=0.68..0.68 rows=8 width=12) (actual time=0.009..0.009 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.002 0.002 ↑ 1.0 8 1

Seq Scan on wf_workflow_instance_log_type (cost=0.00..0.68 rows=8 width=12) (actual time=0.001..0.002 rows=8 loops=1)

27. 0.014 0.035 ↑ 1.0 30 1

Hash (cost=1.18..1.18 rows=30 width=30) (actual time=0.035..0.035 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
28. 0.021 0.021 ↑ 1.0 30 1

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

  • Filter: (wf_pk = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 16
29. 0.029 0.059 ↑ 1.0 67 1

Hash (cost=4.87..4.87 rows=67 width=19) (actual time=0.059..0.059 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
30. 0.030 0.030 ↑ 1.0 67 1

Seq Scan on groups (cost=0.00..4.87 rows=67 width=19) (actual time=0.004..0.030 rows=67 loops=1)

31. 154.280 154.280 ↓ 0.0 0 154,280

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

  • Index Cond: (person_id = ni.owner_user_id)
32.          

CTE stage_complete

33. 21.298 1,266.393 ↓ 5,068.0 5,068 1

Unique (cost=4.29..4.30 rows=1 width=40) (actual time=1,227.738..1,266.393 rows=5,068 loops=1)

34. 70.006 1,245.095 ↓ 81,180.0 81,180 1

Sort (cost=4.29..4.29 rows=1 width=40) (actual time=1,227.736..1,245.095 rows=81,180 loops=1)

  • Sort Key: d.prospect_pk, d.wf_instance_pk, d.stage
  • Sort Method: quicksort Memory: 8467kB
35. 1,175.089 1,175.089 ↓ 81,180.0 81,180 1

CTE Scan on data d (cost=0.00..4.28 rows=1 width=40) (actual time=3.262..1,175.089 rows=81,180 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'::t
  • Rows Removed by Filter: 73100
36.          

CTE recs_distinct

37. 52.969 2,648.936 ↓ 9,282.0 9,282 1

Unique (cost=5.40..5.42 rows=1 width=482) (actual time=2,539.831..2,648.936 rows=9,282 loops=1)

38. 599.658 2,595.967 ↓ 154,280.0 154,280 1

Sort (cost=5.40..5.41 rows=1 width=482) (actual time=2,539.830..2,595.967 rows=154,280 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: 25920kB
39. 149.049 1,996.309 ↓ 154,280.0 154,280 1

Hash Join (cost=0.04..5.39 rows=1 width=482) (actual time=1,790.473..1,996.309 rows=154,280 loops=1)

  • Hash Cond: ((d_1.prospect_pk = ss.prospect_pk) AND (d_1.wf_instance_pk = ss.wf_instance_pk) AND (d_1.stage = ss.stage))
40. 576.799 576.799 ↓ 902.2 154,280 1

CTE Scan on data d_1 (cost=0.00..3.42 rows=171 width=1,010) (actual time=519.990..576.799 rows=154,280 loops=1)

41. 1.817 1,270.461 ↓ 5,068.0 5,068 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=1,270.461..1,270.461 rows=5,068 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 328kB
42. 1,268.644 1,268.644 ↓ 5,068.0 5,068 1

CTE Scan on stage_complete ss (cost=0.00..0.02 rows=1 width=40) (actual time=1,227.740..1,268.644 rows=5,068 loops=1)