explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d5B

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 95,961.023 ↑ 788,610.0 1 1

Nested Loop Left Join (cost=0.43..1,410,470,430.71 rows=788,610 width=1,578) (actual time=273.959..95,961.023 rows=1 loops=1)

  • Join Filter: (i.id = pim_indiv_names.indiv_id)
  • Rows Removed by Join Filter: 16
  • Buffers: shared hit=63728264
2. 0.009 95,807.118 ↑ 788,610.0 1 1

Nested Loop Left Join (cost=0.43..1,410,424,449.39 rows=788,610 width=1,368) (actual time=120.054..95,807.118 rows=1 loops=1)

  • Join Filter: (gender.id = i.gender_id)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=63709891
3. 0.013 95,807.078 ↑ 788,610.0 1 1

Nested Loop Left Join (cost=0.43..1,410,377,131.74 rows=788,610 width=1,182) (actual time=120.024..95,807.078 rows=1 loops=1)

  • Join Filter: (sg.id = p.social_group_id)
  • Rows Removed by Join Filter: 19
  • Buffers: shared hit=63709890
4. 115.891 95,807.012 ↑ 788,610.0 1 1

Nested Loop (cost=0.43..1,410,152,376.65 rows=788,610 width=979) (actual time=119.961..95,807.012 rows=1 loops=1)

  • Buffers: shared hit=63709889
5. 1,057.141 1,057.141 ↓ 1.0 1,577,233 1

Seq Scan on pci_patient p (cost=0.00..38,595.21 rows=1,577,221 width=106) (actual time=0.025..1,057.141 rows=1,577,233 loops=1)

  • Buffers: shared hit=22823
6. 11,040.631 94,633.980 ↓ 0.0 0 1,577,233

Index Scan using pim_individual_pk on pim_individual i (cost=0.43..894.04 rows=1 width=873) (actual time=0.060..0.060 rows=0 loops=1,577,233)

  • Index Cond: (id = p.id)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=63687066
7.          

SubPlan (forIndex Scan)

8. 83,593.349 83,593.349 ↓ 0.0 0 1,577,233

CTE Scan on t t_1 (cost=891.12..893.39 rows=1 width=0) (actual time=0.053..0.053 rows=0 loops=1,577,233)

  • Filter: (id = 26127340)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=57366113
9.          

CTE t

10. 3,559.126 80,438.883 ↑ 25.2 4 1,577,233

Recursive Union (cost=1.84..891.12 rows=101 width=8) (actual time=0.029..0.051 rows=4 loops=1,577,233)

  • Buffers: shared hit=57366113
11. 1,172.040 52,048.689 ↑ 1.0 1 1,577,233

Nested Loop (cost=1.84..41.35 rows=1 width=8) (actual time=0.028..0.033 rows=1 loops=1,577,233)

  • Buffers: shared hit=31350006
12. 1,949.408 42,585.291 ↑ 1.0 1 1,577,233

Nested Loop (cost=1.42..32.89 rows=1 width=4) (actual time=0.023..0.027 rows=1 loops=1,577,233)

  • Buffers: shared hit=25844168
13. 2,257.108 37,853.592 ↑ 1.0 2 1,577,233

Nested Loop (cost=1.28..32.57 rows=2 width=8) (actual time=0.019..0.024 rows=2 loops=1,577,233)

  • Buffers: shared hit=20279586
14. 2,513.496 23,658.495 ↑ 1.0 1 1,577,233

Nested Loop (cost=0.85..20.60 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1,577,233)

  • Buffers: shared hit=13243371
15. 12,617.864 12,617.864 ↑ 1.0 1 1,577,233

Index Scan using pim_party_address_party_id_idx on pim_party_address ppa (cost=0.43..12.15 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=1,577,233)

  • 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=6412411
16. 8,527.135 8,527.135 ↑ 1.0 1 1,705,427

Index Scan using pim_party_pk on pim_party pp (cost=0.43..8.45 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,705,427)

  • Index Cond: (id = i.id)
  • Filter: (type_id = 1)
  • Buffers: shared hit=6830960
17. 11,937.989 11,937.989 ↑ 1.0 2 1,705,427

Index Scan using pim_party_addr_to_addr_type_party_address_idx on pim_party_addr_to_addr_type ppatat (cost=0.43..11.94 rows=2 width=8) (actual time=0.006..0.007 rows=2 loops=1,705,427)

  • Index Cond: (party_address_id = ppa.id)
  • Buffers: shared hit=7036215
18. 2,782.291 2,782.291 ↓ 0.0 0 2,782,291

Index Scan using pim_address_type_pk on pim_address_type padt (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2,782,291)

  • Index Cond: (id = ppatat.address_type_id)
  • Filter: ((code)::text = 'REGISTER'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5564582
19. 8,291.358 8,291.358 ↑ 1.0 1 1,381,893

Index Scan using address_element_pkey on address_element ae (cost=0.43..8.45 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1,381,893)

  • Index Cond: (id = ppa.addr_id)
  • Buffers: shared hit=5505838
20. 5,356.778 24,831.068 ↑ 10.0 1 6,207,767

Nested Loop (cost=0.43..84.77 rows=10 width=8) (actual time=0.003..0.004 rows=1 loops=6,207,767)

  • Buffers: shared hit=26016107
21. 0.000 0.000 ↑ 10.0 1 6,207,767

WorkTable Scan on t (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=6,207,767)

22. 19,474.290 19,474.290 ↑ 1.0 1 6,491,430

Index Scan using address_element_pkey on address_element ae_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=6,491,430)

  • Index Cond: (id = t.p)
  • Filter: (level_id > 1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=26016107
23. 0.037 0.053 ↑ 1.0 19 1

Materialize (cost=0.00..1.28 rows=19 width=203) (actual time=0.031..0.053 rows=19 loops=1)

  • Buffers: shared hit=1
24. 0.016 0.016 ↑ 1.0 19 1

Seq Scan on md_soc_group sg (cost=0.00..1.19 rows=19 width=203) (actual time=0.014..0.016 rows=19 loops=1)

  • Buffers: shared hit=1
25. 0.015 0.031 ↑ 1.0 4 1

Materialize (cost=0.00..1.06 rows=4 width=186) (actual time=0.026..0.031 rows=4 loops=1)

  • Buffers: shared hit=1
26. 0.016 0.016 ↑ 1.0 4 1

Seq Scan on pim_gender gender (cost=0.00..1.04 rows=4 width=186) (actual time=0.013..0.016 rows=4 loops=1)

  • Buffers: shared hit=1
27. 0.034 153.889 ↓ 16.0 16 1

Materialize (cost=0.00..34,152.17 rows=1 width=210) (actual time=153.861..153.889 rows=16 loops=1)

  • Buffers: shared hit=18373
28. 153.855 153.855 ↓ 16.0 16 1

Seq Scan on pim_indiv_names (cost=0.00..34,152.17 rows=1 width=210) (actual time=153.849..153.855 rows=16 loops=1)

  • Filter: (date_end IS NOT NULL)
  • Rows Removed by Filter: 1577929
  • Buffers: shared hit=18373