explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7qsb

Settings
# exclusive inclusive rows x rows loops node
1. 44.517 44.517 ↑ 1,166.3 13,662 1

CTE Scan on cte (cost=1,786,460.55..2,105,144.33 rows=15,934,189 width=32) (actual time=0.141..44.517 rows=13,662 loops=1)

2.          

CTE cte

3. 5.479 40.502 ↑ 1,166.3 13,662 1

Recursive Union (cost=174.09..1,786,460.55 rows=15,934,189 width=32) (actual time=0.139..40.502 rows=13,662 loops=1)

4. 2.565 11.154 ↓ 13.3 13,662 1

Merge Semi Join (cost=174.09..1,649.52 rows=1,029 width=32) (actual time=0.136..11.154 rows=13,662 loops=1)

  • Merge Cond: (a.customer_internal_id = al.parent_customer_internal_id)
5. 2.163 5.213 ↑ 45.2 13,801 1

Merge Join (cost=173.08..185,782.14 rows=623,494 width=40) (actual time=0.062..5.213 rows=13,801 loops=1)

  • Merge Cond: (a.customer_internal_id = aa.parent_customer_internal_id)
6. 0.182 0.182 ↑ 9,946.1 101 1

Index Scan using idx_alignment_customer_id_start_end_date on alignment a (cost=0.56..330,190.27 rows=1,004,555 width=24) (actual time=0.036..0.182 rows=101 loops=1)

  • Index Cond: (((alignment_type)::text = 'explicit'::text) AND (start_date <= '2020-09-09'::date) AND (end_date > '2020-09-09'::date))
  • Filter: (team_internal_id = 103)
7. 2.868 2.868 ↑ 360.0 13,801 1

Index Scan using idx_affiliation_alignment_parent_customer_internal_id on affiliation_alignment aa (cost=0.43..165,550.63 rows=4,968,080 width=16) (actual time=0.023..2.868 rows=13,801 loops=1)

8. 3.376 3.376 ↓ 1.0 13,662 1

Index Scan using idx_affiliation_alignment_parent_customer_internal_id on affiliation_alignment al (cost=0.43..546.66 rows=13,210 width=8) (actual time=0.022..3.376 rows=13,662 loops=1)

  • Index Cond: ((parent_customer_internal_id > 15001) AND (parent_customer_internal_id <= 15,100))
  • Filter: (team_internal_id = 103)
9. 9.432 23.869 ↓ 0.0 0 1

Nested Loop (cost=0.43..146,612.72 rows=1,593,316 width=32) (actual time=23.869..23.869 rows=0 loops=1)

10. 0.775 0.775 ↓ 1.3 13,662 1

WorkTable Scan on cte c (cost=0.00..205.80 rows=10,290 width=24) (actual time=0.002..0.775 rows=13,662 loops=1)

11. 13.662 13.662 ↓ 0.0 0 13,662

Index Scan using idx_affiliation_alignment_parent_customer_internal_id on affiliation_alignment aa_1 (cost=0.43..12.68 rows=155 width=16) (actual time=0.001..0.001 rows=0 loops=13,662)

  • Index Cond: (parent_customer_internal_id = c.customer_internal_id)
  • Filter: (team_internal_id = 103)
Planning time : 0.904 ms
Execution time : 48.958 ms