explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4H7u

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 10,407.266 ↑ 672,717.0 1 1

Nested Loop Left Join (cost=0.43..1,193,060,402.30 rows=672,717 width=2,513) (actual time=10,401.143..10,407.266 rows=1 loops=1)

  • Join Filter: (gender.id = i.gender_id)
  • Rows Removed by Join Filter: 2
  • Buffers: shared hit=12482246 read=44069
2. 0.013 10,407.247 ↑ 672,717.0 1 1

Nested Loop Left Join (cost=0.43..1,193,030,129.00 rows=672,717 width=1,869) (actual time=10,401.126..10,407.247 rows=1 loops=1)

  • Join Filter: (sg.id = p.social_group_id)
  • Rows Removed by Join Filter: 15
  • Buffers: shared hit=12482246 read=44068
3. 755.338 10,407.200 ↑ 672,717.0 1 1

Nested Loop (cost=0.43..1,192,868,675.72 rows=672,717 width=951) (actual time=10,401.084..10,407.200 rows=1 loops=1)

  • Buffers: shared hit=12482246 read=44067
4. 234.727 234.727 ↑ 1.0 1,345,305 1

Seq Scan on pci_patient p (cost=0.00..22,295.34 rows=1,345,434 width=122) (actual time=0.016..234.727 rows=1,345,305 loops=1)

  • Buffers: shared read=8841
5. 2,690.610 9,417.135 ↓ 0.0 0 1,345,305

Index Scan using pim_individual_pkey on pim_individual i (cost=0.43..886.58 rows=1 width=829) (actual time=0.007..0.007 rows=0 loops=1,345,305)

  • Index Cond: (id = p.id)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=12482246 read=35226
6.          

SubPlan (forIndex Scan)

7. 6,726.525 6,726.525 ↓ 0.0 0 1,345,305

CTE Scan on t t_1 (cost=883.80..886.07 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1,345,305)

  • Filter: (id = 1258479)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=7117973 read=10933
8.          

CTE t

9. 1,096.970 6,726.525 ↓ 0.0 0 1,345,305

Recursive Union (cost=1.83..883.80 rows=101 width=8) (actual time=0.004..0.005 rows=0 loops=1,345,305)

  • Buffers: shared hit=7117973 read=10933
10. 0.000 4,035.915 ↓ 0.0 0 1,345,305

Nested Loop (cost=1.83..34.03 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,345,305)

  • Buffers: shared hit=5766313 read=9706
11. 1,236.192 4,035.915 ↓ 0.0 0 1,345,305

Nested Loop (cost=1.40..25.57 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,345,305)

  • Buffers: shared hit=5459954 read=8019
12. 0.000 2,690.610 ↓ 0.0 0 1,345,305

Nested Loop (cost=1.27..25.41 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,345,305)

  • Buffers: shared hit=5241748 read=7999
13. 1,065.829 2,690.610 ↓ 0.0 0 1,345,305

Nested Loop (cost=0.85..16.91 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,345,305)

  • Buffers: shared hit=4728118 read=6116
14. 1,345.305 1,345.305 ↓ 0.0 0 1,345,305

Index Scan using pim_party_address_party_id_idx on pim_party_address ppa (cost=0.42..8.45 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1,345,305)

  • Index Cond: (party_id = i.id)
  • Filter: ((('now'::cstring)::date >= COALESCE(from_date, '-infinity'::timestamp without time zone)) AND (('now'::cstring)::date <= COALESCE(to_date, 'infinity'::timestamp without time zone)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4171927 read=2585
15. 279.476 279.476 ↑ 1.0 1 139,738

Index Scan using pim_party_pkey on pim_party pp (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=139,738)

  • Index Cond: (id = i.id)
  • Filter: (type_id = 1)
  • Buffers: shared hit=556191 read=3531
16. 279.476 279.476 ↑ 2.0 1 139,738

Index Scan using pim_party_addr_to_addr_type_party_address_id_idx on pim_party_addr_to_addr_type ppatat (cost=0.42..8.48 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=139,738)

  • Index Cond: (party_address_id = ppa.id)
  • Buffers: shared hit=513630 read=1883
17. 109.113 109.113 ↑ 1.0 1 109,113

Index Scan using pim_address_type_pkey on pim_address_type padt (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=109,113)

  • Index Cond: (id = ppatat.address_type_id)
  • Filter: ((code)::text = 'REGISTER'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=218206 read=20
18. 187.382 187.382 ↑ 1.0 1 93,691

Index Scan using address_element_pkey on address_element ae (cost=0.43..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=93,691)

  • Index Cond: (id = ppa.addr_id)
  • Buffers: shared hit=306359 read=1687
19. 917.504 1,593.640 ↓ 0.0 0 1,593,640

Nested Loop (cost=0.43..84.77 rows=10 width=8) (actual time=0.001..0.001 rows=0 loops=1,593,640)

  • Buffers: shared hit=1351660 read=1227
20. 0.000 0.000 ↓ 0.0 0 1,593,640

WorkTable Scan on t (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=1,593,640)

21. 676.136 676.136 ↑ 1.0 1 338,068

Index Scan using address_element_pkey on address_element ae_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=338,068)

  • Index Cond: (id = t.p)
  • Filter: (level_id > 1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1351660 read=1227
22. 0.017 0.034 ↑ 1.0 16 1

Materialize (cost=0.00..1.24 rows=16 width=918) (actual time=0.031..0.034 rows=16 loops=1)

  • Buffers: shared read=1
23. 0.017 0.017 ↑ 1.0 16 1

Seq Scan on md_soc_group sg (cost=0.00..1.16 rows=16 width=918) (actual time=0.016..0.017 rows=16 loops=1)

  • Buffers: shared read=1
24. 0.002 0.012 ↑ 1.0 3 1

Materialize (cost=0.00..1.04 rows=3 width=644) (actual time=0.011..0.012 rows=3 loops=1)

  • Buffers: shared read=1
25. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on pim_gender gender (cost=0.00..1.03 rows=3 width=644) (actual time=0.010..0.010 rows=3 loops=1)

  • Buffers: shared read=1