explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wuki

Settings
# exclusive inclusive rows x rows loops node
1. 7,784.740 17,032,193.002 ↓ 37,275.2 186,376 1

Nested Loop (cost=2,786.48..13,896.13 rows=5 width=407) (actual time=349.634..17,032,193.002 rows=186,376 loops=1)

  • Buffers: shared hit=1,999,233,915
2. 1,205.624 17,016,021.342 ↓ 186,376.0 186,376 1

Nested Loop (cost=2,786.47..13,882.99 rows=1 width=148) (actual time=224.846..17,016,021.342 rows=186,376 loops=1)

  • Buffers: shared hit=1,999,233,915
3. 4,021,546.546 17,011,647.326 ↓ 186,376.0 186,376 1

Nested Loop Left Join (cost=2,786.34..13,882.84 rows=1 width=148) (actual time=224.804..17,011,647.326 rows=186,376 loops=1)

  • Join Filter: ((g.externalid)::text = ppn.networkid)
  • Rows Removed by Join Filter: 23,078,572,262
  • Buffers: shared hit=1,999,047,538
4. 0.000 811.836 ↓ 186,376.0 186,376 1

Gather (cost=2,785.92..12,108.16 rows=1 width=148) (actual time=213.265..811.836 rows=186,376 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1,131,450
5. 153.333 1,212.957 ↓ 62,125.0 62,125 3 / 3

Nested Loop (cost=1,785.92..11,108.06 rows=1 width=148) (actual time=218.911..1,212.957 rows=62,125 loops=3)

  • Join Filter: (s.staffid = xsa.staffid)
  • Buffers: shared hit=1,131,450
6. 126.721 376.220 ↓ 116.1 62,128 3 / 3

Hash Join (cost=1,785.50..10,841.70 rows=535 width=179) (actual time=218.824..376.220 rows=62,128 loops=3)

  • Hash Cond: (ppn.providerid = (s.externalid)::text)
  • Buffers: shared hit=385,923
7. 30.871 30.871 ↑ 1.2 62,129 3 / 3

Parallel Seq Scan on planprovidernetwork ppn (cost=0.00..8,759.61 rows=77,661 width=169) (actual time=0.013..30.871 rows=62,129 loops=3)

  • Buffers: shared hit=7,983
8. 41.693 218.628 ↓ 144.8 123,829 3 / 3

Hash (cost=1,774.81..1,774.81 rows=855 width=19) (actual time=218.628..218.628 rows=123,829 loops=3)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7,446kB
  • Buffers: shared hit=377,848
9. 52.808 176.935 ↓ 144.8 123,829 3 / 3

Nested Loop (cost=0.56..1,774.81 rows=855 width=19) (actual time=0.138..176.935 rows=123,829 loops=3)

  • Buffers: shared hit=377,848
10. 0.149 0.149 ↑ 1.0 1 3 / 3

Index Scan using xe_enterprise_data_u1 on xe_enterprise_data ed (cost=0.14..8.98 rows=1 width=4) (actual time=0.085..0.149 rows=1 loops=3)

  • Filter: ((enterpriseid)::text = 'LACARE'::text)
  • Rows Removed by Filter: 144
  • Buffers: shared hit=17
11. 123.978 123.978 ↓ 35.0 123,829 3 / 3

Index Scan using xe_staff_i4 on xe_staff s (cost=0.42..1,730.41 rows=3,542 width=20) (actual time=0.050..123.978 rows=123,829 loops=3)

  • Index Cond: (accountid = ed.accountid)
  • Buffers: shared hit=377,831
12. 683.404 683.404 ↑ 1.0 1 186,383 / 3

Index Scan using pk_xe_staff_addr on xe_staff_addr xsa (cost=0.42..0.49 rows=1 width=25) (actual time=0.011..0.011 rows=1 loops=186,383)

  • Index Cond: ((addressid)::text = md5(concat(ppn.providerid, ppn.addressline1, ppn.addressline2, ppn.city, ppn.state, ppn.zip)))
  • Buffers: shared hit=745,527
13. 12,989,288.944 12,989,288.944 ↓ 35.0 123,829 186,376

Index Scan using xe_staff_i4 on xe_staff g (cost=0.42..1,730.41 rows=3,542 width=14) (actual time=0.015..69.694 rows=123,829 loops=186,376)

  • Index Cond: (accountid = ed.accountid)
  • Buffers: shared hit=1,997,916,088
14. 3,168.392 3,168.392 ↑ 1.0 1 186,376

Index Only Scan using pk_xg_health_plan_group on xg_health_plan_group hpg (cost=0.14..0.15 rows=1 width=14) (actual time=0.017..0.017 rows=1 loops=186,376)

  • Index Cond: (healthplangroupid = ppn.programid)
  • Heap Fetches: 0
  • Buffers: shared hit=186,377
15. 8,386.920 8,386.920 ↑ 5.0 1 186,376

Function Scan on pg_timezone_names (cost=0.00..12.50 rows=5 width=32) (actual time=0.013..0.045 rows=1 loops=186,376)

  • Filter: (name = 'America/Los_Angeles'::text)
  • Rows Removed by Filter: 594