explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jID1 : Index - Parent ID, Record - Physician

Settings
# exclusive inclusive rows x rows loops node
1. 0.255 8,044.764 ↑ 9,480.3 6 1

HashAggregate (cost=3,620,018.62..3,620,587.44 rows=56,882 width=32) (actual time=8,044.565..8,044.764 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=572,831
2.          

CTE bottom_network

3. 0.000 386.345 ↑ 28,380.5 2 1

Recursive Union (cost=1,000.00..394,311.34 rows=56,761 width=32) (actual time=8.234..386.345 rows=2 loops=1)

  • Buffers: shared hit=114,571
4. 4.840 386.375 ↓ 2.0 2 1

Gather (cost=1,000.00..187,168.23 rows=1 width=32) (actual time=8.230..386.375 rows=2 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=114,565
5. 381.535 381.535 ↑ 1.0 1 3 / 3

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

  • Filter: (customer_id = 55,001)
  • Rows Removed by Filter: 4,582,599
  • Buffers: shared hit=114,565
6. 0.005 0.048 ↓ 0.0 0 1

Nested Loop (cost=0.43..20,600.79 rows=5,676 width=32) (actual time=0.048..0.048 rows=0 loops=1)

  • Buffers: shared hit=6
7. 0.003 0.003 ↑ 5.0 2 1

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

8. 0.040 0.040 ↓ 0.0 0 2

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.020..0.020 rows=0 loops=2)

  • Index Cond: (parent_customer_id = a1_1.customer_id)
  • Buffers: shared hit=6
9.          

CTE top_network

10. 0.008 7,658.139 ↑ 20.2 6 1

Recursive Union (cost=1,000.00..3,223,147.59 rows=121 width=32) (actual time=5.394..7,658.139 rows=6 loops=1)

  • Buffers: shared hit=458,260
11. 4.891 367.861 ↓ 2.0 2 1

Gather (cost=1,000.00..187,168.23 rows=1 width=32) (actual time=5.391..367.861 rows=2 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=114,565
12. 362.970 362.970 ↑ 1.0 1 3 / 3

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

  • Filter: (customer_id = 55,001)
  • Rows Removed by Filter: 4,582,599
  • Buffers: shared hit=114,565
13. 3,920.889 7,290.270 ↑ 6.0 2 3

Hash Join (cost=0.33..303,597.70 rows=12 width=32) (actual time=0.248..2,430.090 rows=2 loops=3)

  • Hash Cond: (a2_1.customer_id = a1_3.parent_customer_id)
  • Buffers: shared hit=343,695
14. 3,369.348 3,369.348 ↑ 1.0 13,747,800 3

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

  • Buffers: shared hit=343,695
15. 0.024 0.033 ↑ 5.0 2 3

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.011..0.011 rows=2 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.009 0.009 ↑ 5.0 2 3

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

17. 0.007 8,044.509 ↑ 7,110.2 8 1

Append (cost=0.00..1,990.87 rows=56,882 width=32) (actual time=8.240..8,044.509 rows=8 loops=1)

  • Buffers: shared hit=572,831
18. 386.353 386.353 ↑ 28,380.5 2 1

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

  • Buffers: shared hit=114,571
19. 7,658.149 7,658.149 ↑ 20.2 6 1

CTE Scan on top_network (cost=0.00..2.42 rows=121 width=32) (actual time=5.396..7,658.149 rows=6 loops=1)

  • Buffers: shared hit=458,260