explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mOhg : Index on Parent Id. Query for IDN.

Settings
# exclusive inclusive rows x rows loops node
1. 21.428 870.191 ↑ 1.7 34,053 1

HashAggregate (cost=3,620,018.62..3,620,587.44 rows=56,882 width=32) (actual time=862.741..870.191 rows=34,053 loops=1)

  • Group Key: bottom_network.id, bottom_network.parent_customer_id, bottom_network.customer_id, bottom_network.customer_network_id
  • Buffers: shared hit=331,685
2.          

CTE bottom_network

3. 16.592 469.034 ↑ 1.7 34,053 1

Recursive Union (cost=1,000.00..394,311.34 rows=56,761 width=32) (actual time=375.386..469.034 rows=34,053 loops=1)

  • Buffers: shared hit=217,119
4. 4.745 376.142 ↑ 1.0 1 1

Gather (cost=1,000.00..187,168.23 rows=1 width=32) (actual time=375.383..376.142 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=114,565
5. 371.397 371.397 ↓ 0.0 0 3 / 3

Parallel Seq Scan on customer_network_normalized a1 (cost=0.00..186,168.12 rows=1 width=32) (actual time=371.349..371.397 rows=0 loops=3)

  • Filter: (customer_id = 1)
  • Rows Removed by Filter: 4,582,600
  • Buffers: shared hit=114,565
6. 5.984 76.300 ↓ 1.2 6,810 5

Nested Loop (cost=0.43..20,600.79 rows=5,676 width=32) (actual time=2.720..15.260 rows=6,810 loops=5)

  • Buffers: shared hit=102,554
7. 2.210 2.210 ↓ 681.1 6,811 5

WorkTable Scan on bottom_network a1_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.442 rows=6,811 loops=5)

8. 68.106 68.106 ↑ 568.0 1 34,053

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

  • Index Cond: (parent_customer_id = a1_1.customer_id)
  • Buffers: shared hit=102,554
9.          

CTE top_network

10. 0.000 367.347 ↑ 121.0 1 1

Recursive Union (cost=1,000.00..3,223,147.59 rows=121 width=32) (actual time=366.510..367.347 rows=1 loops=1)

  • Buffers: shared hit=114,566
11. 4.469 367.343 ↑ 1.0 1 1

Gather (cost=1,000.00..187,168.23 rows=1 width=32) (actual time=366.506..367.343 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=114,565
12. 362.874 362.874 ↓ 0.0 0 3 / 3

Parallel Seq Scan on customer_network_normalized a1_2 (cost=0.00..186,168.12 rows=1 width=32) (actual time=362.825..362.874 rows=0 loops=3)

  • Filter: (customer_id = 1)
  • Rows Removed by Filter: 4,582,600
  • Buffers: shared hit=114,565
13. 0.014 0.026 ↓ 0.0 0 1

Hash Join (cost=0.33..303,597.70 rows=12 width=32) (actual time=0.025..0.026 rows=0 loops=1)

  • Hash Cond: (a2_1.customer_id = a1_3.parent_customer_id)
  • Buffers: shared hit=1
14. 0.007 0.007 ↑ 13,747,800.0 1 1

Seq Scan on customer_network_normalized a2_1 (cost=0.00..252,043.00 rows=13,747,800 width=32) (actual time=0.007..0.007 rows=1 loops=1)

  • Buffers: shared hit=1
15. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
16. 0.004 0.004 ↑ 10.0 1 1

WorkTable Scan on top_network a1_3 (cost=0.00..0.20 rows=10 width=8) (actual time=0.003..0.004 rows=1 loops=1)

17. 2.860 848.763 ↑ 1.7 34,054 1

Append (cost=0.00..1,990.87 rows=56,882 width=32) (actual time=375.389..848.763 rows=34,054 loops=1)

  • Buffers: shared hit=331,685
18. 478.553 478.553 ↑ 1.7 34,053 1

CTE Scan on bottom_network (cost=0.00..1,135.22 rows=56,761 width=32) (actual time=375.388..478.553 rows=34,053 loops=1)

  • Buffers: shared hit=217,119
19. 367.350 367.350 ↑ 121.0 1 1

CTE Scan on top_network (cost=0.00..2.42 rows=121 width=32) (actual time=366.513..367.350 rows=1 loops=1)

  • Buffers: shared hit=114,566