explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IzvW

Settings
# exclusive inclusive rows x rows loops node
1. 73,456.141 78,407.796 ↑ 1.1 3,102,106 1

Gather (cost=108,782.93..17,659,634.77 rows=3,343,351 width=28) (actual time=1,287.108..78,407.796 rows=3,102,106 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
2. 1,540.751 4,951.633 ↑ 1.4 775,526 4

Hash Anti Join (cost=107,782.93..352,961.06 rows=1,078,500 width=24) (actual time=1,270.003..4,951.633 rows=775,526 loops=4)

  • Hash Cond: ((t.id = dr.deal_id) AND (dfs.deal_funnel_id = dr.deal_funnel_id) AND (dfs.id = dr.deal_funnel_status_id))
3. 1,108.997 2,165.106 ↑ 1.2 919,870 4

Hash Join (cost=1,058.47..106,333.39 rows=1,125,675 width=28) (actual time=13.141..2,165.106 rows=919,870 loops=4)

  • 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: 2477751
4. 842.568 1,049.265 ↑ 1.3 3,401,209 4

Hash Join (cost=497.49..94,372.81 rows=4,341,625 width=36) (actual time=6.110..1,049.265 rows=3,401,209 loops=4)

  • Hash Cond: (t.deal_funnel_id = dfs.deal_funnel_id)
5. 200.667 200.667 ↑ 1.3 345,274 4

Parallel Seq Scan on deal t (cost=0.00..30,715.75 rows=451,275 width=20) (actual time=0.005..200.667 rows=345,274 loops=4)

6. 2.498 6.030 ↑ 1.0 10,327 4

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

  • Buckets: 16384 Batches: 1 Memory Usage: 613kB
7. 3.532 3.532 ↑ 1.0 10,327 4

Seq Scan on deal_funnel_status dfs (cost=0.00..368.40 rows=10,327 width=16) (actual time=0.006..3.532 rows=10,327 loops=4)

  • Filter: ((NOT is_removed) AND (status_type <> 'failure_analysis'::analytics.deal_funnel_status_type_mnemonic))
  • Rows Removed by Filter: 1545
8. 2.520 6.844 ↓ 1.1 10,885 4

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

  • Buckets: 16384 Batches: 1 Memory Usage: 596kB
9. 4.324 4.324 ↓ 1.1 10,885 4

Seq Scan on deal_funnel_status dfsu (cost=0.00..442.60 rows=9,471 width=12) (actual time=0.022..4.324 rows=10,885 loops=4)

  • 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
10. 741.437 1,245.776 ↑ 1.0 2,603,544 4

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

  • Buckets: 2097152 Batches: 4 Memory Usage: 44256kB
11. 504.339 504.339 ↑ 1.0 2,603,544 4

Seq Scan on deal_route dr (cost=0.00..48,449.44 rows=2,603,544 width=12) (actual time=0.016..504.339 rows=2,603,544 loops=4)

12.          

SubPlan (forGather)

13. 0.001 0.022 ↑ 1.0 1 3,102,106

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

14. 0.003 0.021 ↑ 1.0 1 3,102,106

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

  • Sort Key: drs.sort
  • Sort Method: quicksort Memory: 25kB
15. 0.002 0.018 ↑ 1.0 1 3,102,106

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

16. 0.014 0.014 ↓ 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.012..0.014 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
17. 0.002 0.002 ↑ 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