explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Qpe

Settings
# exclusive inclusive rows x rows loops node
1. 814.810 814.810 ↑ 1,189.0 13,662 1

CTE Scan on cte (cost=1,969,418.61..2,294,296.39 rows=16,243,889 width=32) (actual time=777.672..814.810 rows=13,662 loops=1)

2.          

CTE cte

3. 6.001 812.518 ↑ 1,189.0 13,662 1

Recursive Union (cost=1,824.01..1,969,418.61 rows=16,243,889 width=32) (actual time=777.670..812.518 rows=13,662 loops=1)

4. 405.680 782.783 ↓ 13.0 13,662 1

Gather (cost=1,824.01..154,288.58 rows=1,049 width=32) (actual time=777.667..782.783 rows=13,662 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.602 377.103 ↓ 10.4 4,554 3 / 3

Nested Loop (cost=824.01..153,183.68 rows=437 width=32) (actual time=370.085..377.103 rows=4,554 loops=3)

6. 25.638 375.445 ↑ 21.3 33 3 / 3

Hash Join (cost=823.58..149,740.51 rows=704 width=32) (actual time=370.063..375.445 rows=33 loops=3)

  • Hash Cond: (a.customer_internal_id = al.parent_customer_internal_id)
7. 344.105 344.105 ↑ 1.2 345,333 3 / 3

Parallel Seq Scan on alignment a (cost=0.00..147,310.67 rows=426,796 width=24) (actual time=80.509..344.105 rows=345,333 loops=3)

  • Filter: ((start_date <= '2020-09-09'::date) AND (end_date > '2020-09-09'::date) AND ((alignment_type)::text = 'explicit'::text) AND (team_internal_id = 103))
  • Rows Removed by Filter: 2,322,667
8. 0.018 5.702 ↑ 109.5 99 3 / 3

Hash (cost=688.08..688.08 rows=10,840 width=8) (actual time=5.702..5.702 rows=99 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 132kB
9. 1.825 5.684 ↑ 109.5 99 3 / 3

HashAggregate (cost=579.68..688.08 rows=10,840 width=8) (actual time=5.619..5.684 rows=99 loops=3)

  • Group Key: al.parent_customer_internal_id
10. 3.859 3.859 ↓ 1.0 13,662 3 / 3

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.069..3.859 rows=13,662 loops=3)

  • Index Cond: ((parent_customer_internal_id > 15001) AND (parent_customer_internal_id <= 15,100))
  • Filter: (team_internal_id = 103)
11. 1.056 1.056 ↑ 1.1 138 99 / 3

Index Scan using idx_affiliation_alignment_parent_customer_internal_id on affiliation_alignment aa (cost=0.43..3.34 rows=155 width=16) (actual time=0.010..0.032 rows=138 loops=99)

  • Index Cond: (parent_customer_internal_id = a.customer_internal_id)
12. 9.219 23.734 ↓ 0.0 0 1

Nested Loop (cost=0.43..149,025.23 rows=1,624,284 width=32) (actual time=23.734..23.734 rows=0 loops=1)

13. 0.853 0.853 ↓ 1.3 13,662 1

WorkTable Scan on cte c (cost=0.00..209.80 rows=10,490 width=24) (actual time=0.003..0.853 rows=13,662 loops=1)

14. 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.64 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.854 ms
Execution time : 819.173 ms