explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OncU

Settings
# exclusive inclusive rows x rows loops node
1. 2,995.654 50,773.844 ↑ 1.1 3,102,106 1

Hash Anti Join (cost=95,069.93..17,684,309.87 rows=3,343,351 width=28) (actual time=1,419.140..50,773.844 rows=3,102,106 loops=1)

  • Hash Cond: ((t.id = dr.deal_id) AND (dfs.deal_funnel_id = dr.deal_funnel_id) AND (dfs.id = dr.deal_funnel_status_id))
2. 4,902.517 9,165.847 ↓ 1.1 3,679,479 1

Hash Join (cost=1,058.47..272,384.21 rows=3,489,591 width=28) (actual time=10.890..9,165.847 rows=3,679,479 loops=1)

  • Hash Cond: (t.deal_funnel_status_id = dfsu.id)
  • Join Filter: ((dfs.sort < dfsu.sort) AND ((dfsu.status_type = ANY ('{intermediate,success,initial}'::analytics.deal_funnel_status_type_mnemonic[])) OR ((dfsu.status_type = 'failure'::analytics.deal_funnel_status_type_mnemonic) AND (dfs.status_type = 'initial'::analytics.deal_funnel_status_type_mnemonic)) OR ((dfsu.status_type = 'failure_analysis'::analytics.deal_funnel_status_type_mnemonic) AND (dfs.status_type = ANY ('{initial,failure}'::analytics.deal_funnel_status_type_mnemonic[])))))
  • Rows Removed by Join Filter: 9911003
3. 3,650.676 4,257.576 ↓ 1.0 13,604,837 1

Hash Join (cost=497.49..236,484.48 rows=13,459,038 width=36) (actual time=5.091..4,257.576 rows=13,604,837 loops=1)

  • Hash Cond: (t.deal_funnel_id = dfs.deal_funnel_id)
4. 601.891 601.891 ↑ 1.0 1,381,098 1

Seq Scan on deal t (cost=0.00..40,192.51 rows=1,398,951 width=20) (actual time=0.005..601.891 rows=1,381,098 loops=1)

5. 2.095 5.009 ↑ 1.0 10,327 1

Hash (cost=368.40..368.40 rows=10,327 width=16) (actual time=5.009..5.009 rows=10,327 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 613kB
6. 2.914 2.914 ↑ 1.0 10,327 1

Seq Scan on deal_funnel_status dfs (cost=0.00..368.40 rows=10,327 width=16) (actual time=0.004..2.914 rows=10,327 loops=1)

  • Filter: ((NOT is_removed) AND (status_type <> 'failure_analysis'::analytics.deal_funnel_status_type_mnemonic))
  • Rows Removed by Filter: 1545
7. 2.105 5.754 ↓ 1.1 10,885 1

Hash (cost=442.60..442.60 rows=9,471 width=12) (actual time=5.754..5.754 rows=10,885 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 596kB
8. 3.649 3.649 ↓ 1.1 10,885 1

Seq Scan on deal_funnel_status dfsu (cost=0.00..442.60 rows=9,471 width=12) (actual time=0.013..3.649 rows=10,885 loops=1)

  • Filter: ((NOT is_removed) AND ((status_type = ANY ('{intermediate,success,initial}'::analytics.deal_funnel_status_type_mnemonic[])) OR (status_type = 'failure'::analytics.deal_funnel_status_type_mnemonic) OR (status_type = 'failure_analysis'::analytics.deal_funnel_status_type_mnemonic)))
  • Rows Removed by Filter: 987
9. 814.874 1,387.071 ↑ 1.0 2,603,544 1

Hash (cost=48,449.44..48,449.44 rows=2,603,544 width=12) (actual time=1,387.071..1,387.071 rows=2,603,544 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 144640kB
10. 572.197 572.197 ↑ 1.0 2,603,544 1

Seq Scan on deal_route dr (cost=0.00..48,449.44 rows=2,603,544 width=12) (actual time=0.007..572.197 rows=2,603,544 loops=1)

11.          

SubPlan (forHash Anti Join)

12. 3,102.106 37,225.272 ↑ 1.0 1 3,102,106

Limit (cost=5.07..5.08 rows=1 width=12) (actual time=0.011..0.012 rows=1 loops=3,102,106)

13. 9,306.318 34,123.166 ↑ 1.0 1 3,102,106

Sort (cost=5.07..5.08 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=3,102,106)

  • Sort Key: drs.sort
  • Sort Method: quicksort Memory: 25kB
14. 787.264 24,816.848 ↑ 1.0 1 3,102,106

Nested Loop (cost=0.72..5.06 rows=1 width=12) (actual time=0.006..0.008 rows=1 loops=3,102,106)

15. 12,408.424 12,408.424 ↓ 2.0 2 3,102,106

Index Scan using fki_deal_route__deal_id on deal_route dr_1 (cost=0.43..2.76 rows=1 width=12) (actual time=0.003..0.004 rows=2 loops=3,102,106)

  • Index Cond: (deal_id = t.id)
  • Filter: (deal_funnel_id = t.deal_funnel_id)
  • Rows Removed by Filter: 0
16. 11,621.160 11,621.160 ↑ 1.0 1 5,810,580

Index Scan using pk_deal_funnel_status on deal_funnel_status drs (cost=0.29..2.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,810,580)

  • Index Cond: (id = dr_1.deal_funnel_status_id)
  • Filter: ((NOT is_removed) AND (sort > dfs.sort))
  • Rows Removed by Filter: 0