explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uONj

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Result (cost=9,059.11..243,091,604.77 rows=2,795,883 width=505) (actual rows= loops=)

  • One-Time Filter: nfc.f4role_unitprivs8check('pers.person'::character varying)
  • Functions: 177
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=9,059.11..27,563,974.01 rows=2,795,883 width=326) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Hash Join (cost=9,050.53..4,176,412.46 rows=2,795,883 width=325) (actual rows= loops=)

  • Hash Cond: (main.gender = tb_1.frllo_id)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,018.75..4,076,438.30 rows=834,592 width=321) (actual rows= loops=)

  • Hash Cond: (reg.id = monetization.r_register_id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,017.71..4,073,307.14 rows=834,592 width=289) (actual rows= loops=)

  • Hash Cond: (reg.id = bnf.reg_id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,241.19..4,063,339.37 rows=834,592 width=185) (actual rows= loops=)

  • Hash Cond: (reg.region_id = tb_2.frllo_id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,233.40..4,052,804.60 rows=834,592 width=185) (actual rows= loops=)

  • Hash Cond: (reg.id = reg_1.id)
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=75.04..4,049,454.99 rows=834,592 width=153) (actual rows= loops=)

  • Merge Cond: (main.id = main_2.pid)
9. 0.000 0.000 ↓ 0.0

Merge Join (cost=74.35..4,045,245.38 rows=834,592 width=157) (actual rows= loops=)

  • Merge Cond: (reg.person = main.id)
10. 0.000 0.000 ↓ 0.0

Index Scan using i4reg8person on reg (cost=0.29..1,075.09 rows=20,766 width=52) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Materialize (cost=74.06..4,033,711.14 rows=843,755 width=105) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=74.06..4,031,601.75 rows=843,755 width=105) (actual rows= loops=)

  • Merge Cond: (main.id = main_1.pid)
13. 0.000 0.000 ↓ 0.0

Index Scan using pk4person on person main (cost=0.29..8,274.71 rows=20,994 width=85) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Materialize (cost=73.78..4,354,511.21 rows=843,755 width=28) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=73.78..4,352,401.83 rows=843,755 width=28) (actual rows= loops=)

  • Join Filter: (f.id = fp.code)
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=73.52..555,504.07 rows=168,751 width=44) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Scan using pk4person on person p (cost=0.29..8,274.71 rows=20,994 width=16) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=73.23..99.21 rows=8 width=36) (actual rows= loops=)

  • Hash Cond: (f.frllo_id = main_1.policy_type)
19. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_245 f (cost=0.00..16.70 rows=670 width=117) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=73.21..73.21 rows=2 width=32) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=46.89..73.21 rows=2 width=32) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using i4person_insurance8pid on person_insurance main_1 (cost=0.29..0.34 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (pid = p.id)
23. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=46.61..72.86 rows=1 width=8) (actual rows= loops=)

  • Hash Cond: (fs.code = tb.id)
  • Filter: (tb.frllo_id = main_1.ins_org)
24. 0.000 0.000 ↓ 0.0

Function Scan on f4ref_med_ins_org fs (cost=0.26..10.26 rows=1,000 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=43.14..43.14 rows=257 width=12) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Subquery Scan on tb (cost=0.00..43.14 rows=257 width=12) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_183 f_1 (cost=0.00..40.57 rows=257 width=549) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Function Scan on f4ref_policy_types fp (cost=0.26..10.26 rows=1,000 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.69..1,113.65 rows=1,700 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Result (cost=0.69..1,109.40 rows=1,700 width=12) (actual rows= loops=)

  • One-Time Filter: nfc.f4role_unitprivs8check('pers.person_mo_attach'::character varying)
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.69..1,109.40 rows=1,700 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Only Scan using i4person_mo_attach8pid on person_mo_attach main_2 (cost=0.15..69.65 rows=1,700 width=12) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Only Scan using pk4fnsi_1_2_643_5_1_13_13_11_1461 on fnsi_1_2_643_5_1_13_13_11_1461 ref_mo (cost=0.29..0.60 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (frllo_id = main_2.mo)
34. 0.000 0.000 ↓ 0.0

Hash (cost=898.98..898.98 rows=20,750 width=40) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on reg reg_1 (cost=0.00..898.98 rows=20,750 width=40) (actual rows= loops=)

  • Filter: ((COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) >= include_date) AND ((exclude_date IS NULL) OR ((exclude_date IS NOT NULL) AND (COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) <= exclude_date))))
36. 0.000 0.000 ↓ 0.0

Hash (cost=6.72..6.72 rows=86 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_2 (cost=0.00..6.72 rows=86 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_206 f_2 (cost=0.00..5.86 rows=86 width=249) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=7,551.14..7,551.14 rows=18,030 width=112) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Subquery Scan on bnf (cost=6,512.89..7,551.14 rows=18,030 width=112) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,512.89..7,370.84 rows=18,030 width=112) (actual rows= loops=)

  • Group Key: benefits.reg_id
42. 0.000 0.000 ↓ 0.0

Sort (cost=6,512.89..6,558.09 rows=18,081 width=289) (actual rows= loops=)

  • Sort Key: benefits.reg_id
43. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,328.16..2,759.86 rows=18,081 width=289) (actual rows= loops=)

  • Hash Cond: (benefits.disease_id = tb_4.frllo_id)
44. 0.000 0.000 ↓ 0.0

Hash Join (cost=26.97..1,210.26 rows=18,081 width=170) (actual rows= loops=)

  • Hash Cond: (benefits.ben_type_id = tb_3.frllo_id)
45. 0.000 0.000 ↓ 0.0

Seq Scan on benefits (cost=0.00..934.67 rows=18,081 width=20) (actual rows= loops=)

  • Filter: ((COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) >= receive_date) AND ((cancel_date IS NULL) OR ((cancel_date IS NOT NULL) AND (COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) <= cancel_date))))
46. 0.000 0.000 ↓ 0.0

Hash (cost=25.06..25.06 rows=153 width=166) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_3 (cost=0.00..25.06 rows=153 width=166) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_541 f_3 (cost=0.00..23.53 rows=153 width=328) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=1,114.58..1,114.58 rows=14,929 width=131) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_4 (cost=0.00..1,114.58 rows=14,929 width=131) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_11_1005 f_4 (cost=0.00..965.29 rows=14,929 width=256) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=1.03..1.03 rows=1 width=40) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on monetization (cost=0.00..1.03 rows=1 width=40) (actual rows= loops=)

  • Filter: ((COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) >= start_date) AND ((end_date IS NULL) OR ((end_date IS NOT NULL) AND (COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) <= end_date))))
54. 0.000 0.000 ↓ 0.0

Hash (cost=23.40..23.40 rows=670 width=12) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_1 (cost=0.00..23.40 rows=670 width=12) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_11_1040 f_5 (cost=0.00..16.70 rows=670 width=117) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Unique (cost=8.34..8.35 rows=1 width=1) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Sort (cost=8.34..8.34 rows=1 width=1) (actual rows= loops=)

  • Sort Key: (CASE WHEN (reg_2.id IS NOT NULL) THEN true ELSE false END)
59. 0.000 0.000 ↓ 0.0

Index Scan using i4reg8person on reg reg_2 (cost=0.29..8.33 rows=1 width=1) (actual rows= loops=)

  • Index Cond: (person = reg.person)
  • Filter: ((id <> reg.id) AND (COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) >= include_date) AND ((exclude_date IS NULL) OR ((exclude_date IS NOT NULL) AND (COALESCE((to_date('2020-10-15'::text, 'yyyy-mm-dd'::text))::timestamp with time zone, now()) <= exclude_date))))
60.          

SubPlan (for Result)

61. 0.000 0.000 ↓ 0.0

Function Scan on f4ref_sex ft (cost=0.26..12.76 rows=5 width=32) (actual rows= loops=)

  • Filter: (code = tb_1.id)
62. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.21..21.01 rows=5 width=32) (actual rows= loops=)

  • Hash Cond: (f2.code = tb_5.id)
63. 0.000 0.000 ↓ 0.0

Function Scan on f4ref_region f2 (cost=0.26..10.26 rows=1,000 width=36) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=6.94..6.94 rows=1 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_5 (cost=0.00..6.94 rows=1 width=4) (actual rows= loops=)

  • Filter: (tb_5.frllo_id = reg.region_id)
66. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_206 f_6 (cost=0.00..5.86 rows=86 width=249) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_6 (cost=0.00..6.94 rows=1 width=4) (actual rows= loops=)

  • Filter: (reg.region_id = tb_6.frllo_id)
68. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_206 f_7 (cost=0.00..5.86 rows=86 width=249) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash Join (cost=22.45..36.34 rows=15 width=32) (actual rows= loops=)

  • Hash Cond: ((mder.code)::text = (tb_7.code)::text)
70. 0.000 0.000 ↓ 0.0

Function Scan on f4ref_exclusion_reason mder (cost=0.26..10.26 rows=1,000 width=64) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=22.15..22.15 rows=3 width=32) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Subquery Scan on tb_7 (cost=0.00..22.15 rows=3 width=32) (actual rows= loops=)

  • Filter: (tb_7.frllo_id = reg.exclude_reason)
73. 0.000 0.000 ↓ 0.0

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_623 f_8 (cost=0.00..15.40 rows=540 width=145) (actual rows= loops=)