explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pkb7

Settings
# exclusive inclusive rows x rows loops node
1. 16.925 118.266 ↑ 1.7 34,053 1

HashAggregate (cost=210,428.17..210,996.99 rows=56,882 width=24) (actual time=112.220..118.266 rows=34,053 loops=1)

  • Group Key: bottom_network.parent_customer_id, bottom_network.customer_id, bottom_network.customer_network_id
  • Buffers: shared hit=102,562
2.          

CTE top_network

3. 0.001 0.033 ↑ 121.0 1 1

Recursive Union (cost=0.43..859.12 rows=121 width=24) (actual time=0.022..0.033 rows=1 loops=1)

  • Buffers: shared hit=4
4. 0.021 0.021 ↑ 1.0 1 1

Index Scan using customer_network_normalized_customer_id on customer_network_normalized a1 (cost=0.43..8.45 rows=1 width=24) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (customer_id = 1)
  • Buffers: shared hit=4
5. 0.002 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.43..84.82 rows=12 width=24) (actual time=0.011..0.011 rows=0 loops=1)

6. 0.001 0.001 ↑ 10.0 1 1

WorkTable Scan on top_network a1_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.001 rows=1 loops=1)

7. 0.008 0.008 ↓ 0.0 0 1

Index Scan using customer_network_normalized_customer_id on customer_network_normalized a2 (cost=0.43..8.45 rows=1 width=24) (actual time=0.007..0.008 rows=0 loops=1)

  • Index Cond: (customer_id = a1_1.parent_customer_id)
8.          

CTE bottom_network

9. 12.667 89.474 ↑ 1.7 34,053 1

Recursive Union (cost=0.43..207,151.56 rows=56,761 width=24) (actual time=0.024..89.474 rows=34,053 loops=1)

  • Buffers: shared hit=102,558
10. 0.022 0.022 ↑ 1.0 1 1

Index Scan using customer_network_normalized_customer_id on customer_network_normalized a1_2 (cost=0.43..8.45 rows=1 width=24) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (customer_id = 1)
  • Buffers: shared hit=4
11. 40.287 76.785 ↓ 1.2 6,810 5

Nested Loop (cost=0.43..20,600.79 rows=5,676 width=24) (actual time=2.739..15.357 rows=6,810 loops=5)

  • Buffers: shared hit=102,554
12. 2.445 2.445 ↓ 681.1 6,811 5

WorkTable Scan on bottom_network a1_3 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.489 rows=6,811 loops=5)

13. 34.053 34.053 ↑ 568.0 1 34,053

Index Scan using customer_network_normalized_parent_id on customer_network_normalized a2_1 (cost=0.43..2,054.38 rows=568 width=24) (actual time=0.001..0.001 rows=1 loops=34,053)

  • Index Cond: (parent_customer_id = a1_3.customer_id)
  • Buffers: shared hit=102,554
14. 2.852 101.341 ↑ 1.7 34,054 1

Append (cost=0.00..1,990.87 rows=56,882 width=24) (actual time=0.025..101.341 rows=34,054 loops=1)

  • Buffers: shared hit=102,562
15. 98.455 98.455 ↑ 1.7 34,053 1

CTE Scan on bottom_network (cost=0.00..1,135.22 rows=56,761 width=24) (actual time=0.025..98.455 rows=34,053 loops=1)

  • Buffers: shared hit=102,558
16. 0.034 0.034 ↑ 121.0 1 1

CTE Scan on top_network (cost=0.00..2.42 rows=121 width=24) (actual time=0.023..0.034 rows=1 loops=1)

  • Buffers: shared hit=4