explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BV78 : Index - Child ID, Record - IDN

Settings
# exclusive inclusive rows x rows loops node
1. 20.939 12,704.377 ↑ 1.7 34,053 1

HashAggregate (cost=3,041,105.83..3,041,674.65 rows=56,882 width=32) (actual time=12,696.870..12,704.377 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=572,833
2.          

CTE bottom_network

3. 16.416 12,670.993 ↑ 1.7 34,053 1

Recursive Union (cost=0.43..3,037,687.02 rows=56,761 width=32) (actual time=0.029..12,670.993 rows=34,053 loops=1)

  • Buffers: shared hit=572,829
4. 0.027 0.027 ↑ 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=32) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (customer_id = 1)
  • Buffers: shared hit=4
5. 6,929.170 12,654.550 ↓ 1.2 6,810 5

Hash Join (cost=0.33..303,654.34 rows=5,676 width=32) (actual time=515.655..2,530.910 rows=6,810 loops=5)

  • Hash Cond: (a2.parent_customer_id = a1_1.customer_id)
  • Buffers: shared hit=572,825
6. 5,717.350 5,717.350 ↑ 1.0 13,747,800 5

Seq Scan on customer_network_normalized a2 (cost=0.00..252,043.00 rows=13,747,800 width=32) (actual time=0.006..1,143.470 rows=13,747,800 loops=5)

  • Buffers: shared hit=572,825
7. 4.840 8.030 ↓ 681.1 6,811 5

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=1.606..1.606 rows=6,811 loops=5)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 352kB
8. 3.190 3.190 ↓ 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.638 rows=6,811 loops=5)

9.          

CTE top_network

10. 0.004 0.061 ↑ 121.0 1 1

Recursive Union (cost=0.43..859.12 rows=121 width=32) (actual time=0.043..0.061 rows=1 loops=1)

  • Buffers: shared hit=4
11. 0.040 0.040 ↑ 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=32) (actual time=0.039..0.040 rows=1 loops=1)

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

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

13. 0.003 0.003 ↑ 10.0 1 1

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

14. 0.012 0.012 ↓ 0.0 0 1

Index Scan using customer_network_normalized_customer_id on customer_network_normalized a2_1 (cost=0.43..8.45 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (customer_id = a1_3.parent_customer_id)
15. 2.486 12,683.438 ↑ 1.7 34,054 1

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

  • Buffers: shared hit=572,833
16. 12,680.888 12,680.888 ↑ 1.7 34,053 1

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

  • Buffers: shared hit=572,829
17. 0.064 0.064 ↑ 121.0 1 1

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

  • Buffers: shared hit=4