explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z93

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 85,408.817 ↑ 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=69.559..85,408.817 rows=1 loops=1)

  • Join Filter: (gender.id = i.gender_id)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=63709891
2. 0.010 85,408.801 ↑ 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=69.545..85,408.801 rows=1 loops=1)

  • Join Filter: (sg.id = p.social_group_id)
  • Rows Removed by Join Filter: 19
  • Buffers: shared hit=63709890
3. 934.203 85,408.739 ↑ 788,610.0 1 1

Nested Loop (cost=0.43..1,410,152,376.65 rows=788,610 width=979) (actual time=69.486..85,408.739 rows=1 loops=1)

  • Buffers: shared hit=63709889
4. 881.187 881.187 ↓ 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.017..881.187 rows=1,577,233 loops=1)

  • Buffers: shared hit=22823
5. 9,463.398 83,593.349 ↓ 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.053..0.053 rows=0 loops=1,577,233)

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

SubPlan (forIndex Scan)

7. 74,129.951 74,129.951 ↓ 0.0 0 1,577,233

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

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

CTE t

9. 6,612.427 70,975.485 ↑ 25.2 4 1,577,233

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

  • Buffers: shared hit=57366113
10. 976.700 45,739.757 ↑ 1.0 1 1,577,233

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

  • Buffers: shared hit=31350006
11. 3,526.641 37,853.592 ↑ 1.0 1 1,577,233

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

  • Buffers: shared hit=25844168
12. 808.069 31,544.660 ↑ 1.0 2 1,577,233

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

  • Buffers: shared hit=20279586
13. 2,641.690 20,504.029 ↑ 1.0 1 1,577,233

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

  • Buffers: shared hit=13243371
14. 11,040.631 11,040.631 ↑ 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.006..0.007 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
15. 6,821.708 6,821.708 ↑ 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.004..0.004 rows=1 loops=1,705,427)

  • Index Cond: (id = i.id)
  • Filter: (type_id = 1)
  • Buffers: shared hit=6830960
16. 10,232.562 10,232.562 ↑ 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.006 rows=2 loops=1,705,427)

  • Index Cond: (party_address_id = ppa.id)
  • Buffers: shared hit=7036215
17. 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
18. 6,909.465 6,909.465 ↑ 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.005 rows=1 loops=1,381,893)

  • Index Cond: (id = ppa.addr_id)
  • Buffers: shared hit=5505838
19. 0.000 18,623.301 ↑ 10.0 1 6,207,767

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

  • Buffers: shared hit=26016107
20. 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)

21. 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.002..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
22. 0.035 0.052 ↑ 1.0 19 1

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

  • Buffers: shared hit=1
23. 0.017 0.017 ↑ 1.0 19 1

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

  • Buffers: shared hit=1
24. 0.008 0.012 ↑ 1.0 4 1

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

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

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

  • Buffers: shared hit=1