explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q5qs : Index - Child ID, Record - Physician

Settings
# exclusive inclusive rows x rows loops node
1. 0.238 2,440.690 ↑ 9,480.3 6 1

HashAggregate (cost=3,041,105.83..3,041,674.65 rows=56,882 width=32) (actual time=2,440.497..2,440.690 rows=6 loops=1)

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

CTE bottom_network

3. 0.003 2,440.357 ↑ 28,380.5 2 1

Recursive Union (cost=0.43..3,037,687.02 rows=56,761 width=32) (actual time=0.030..2,440.357 rows=2 loops=1)

  • Buffers: shared hit=114,570
4. 0.031 0.031 ↓ 2.0 2 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.028..0.031 rows=2 loops=1)

  • Index Cond: (customer_id = 55,001)
  • Buffers: shared hit=5
5. 1,346.861 2,440.323 ↓ 0.0 0 1

Hash Join (cost=0.33..303,654.34 rows=5,676 width=32) (actual time=2,440.322..2,440.323 rows=0 loops=1)

  • Hash Cond: (a2.parent_customer_id = a1_1.customer_id)
  • Buffers: shared hit=114,565
6. 1,093.456 1,093.456 ↑ 1.0 13,747,800 1

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

  • Buffers: shared hit=114,565
7. 0.004 0.006 ↑ 5.0 2 1

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.002 0.002 ↑ 5.0 2 1

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

9.          

CTE top_network

10. 0.013 0.085 ↑ 20.2 6 1

Recursive Union (cost=0.43..859.12 rows=121 width=32) (actual time=0.037..0.085 rows=6 loops=1)

  • Buffers: shared hit=25
11. 0.036 0.036 ↓ 2.0 2 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.033..0.036 rows=2 loops=1)

  • Index Cond: (customer_id = 55,001)
  • Buffers: shared hit=5
12. 0.009 0.036 ↑ 6.0 2 3

Nested Loop (cost=0.43..84.82 rows=12 width=32) (actual time=0.008..0.012 rows=2 loops=3)

  • Buffers: shared hit=20
13. 0.003 0.003 ↑ 5.0 2 3

WorkTable Scan on top_network a1_3 (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.001 rows=2 loops=3)

14. 0.024 0.024 ↑ 1.0 1 6

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.004..0.004 rows=1 loops=6)

  • Index Cond: (customer_id = a1_3.parent_customer_id)
  • Buffers: shared hit=20
15. 0.003 2,440.452 ↑ 7,110.2 8 1

Append (cost=0.00..1,990.87 rows=56,882 width=32) (actual time=0.032..2,440.452 rows=8 loops=1)

  • Buffers: shared hit=114,595
16. 2,440.359 2,440.359 ↑ 28,380.5 2 1

CTE Scan on bottom_network (cost=0.00..1,135.22 rows=56,761 width=32) (actual time=0.032..2,440.359 rows=2 loops=1)

  • Buffers: shared hit=114,570
17. 0.090 0.090 ↑ 20.2 6 1

CTE Scan on top_network (cost=0.00..2.42 rows=121 width=32) (actual time=0.039..0.090 rows=6 loops=1)

  • Buffers: shared hit=25