explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4iB4

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

Sort (cost=25,755.38..25,755.38 rows=1 width=40) (actual rows= loops=)

  • Sort Key: plp.srl
2. 0.000 0.000 ↓ 0.0

Subquery Scan on plp (cost=25,752.18..25,755.37 rows=1 width=40) (actual rows= loops=)

  • Filter: (plp.rn = 1)
3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=25,752.18..25,754.14 rows=98 width=48) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=25,752.18..25,752.43 rows=98 width=40) (actual rows= loops=)

  • Sort Key: a."OWNER_ID", (row_number() OVER (?))
5. 0.000 0.000 ↓ 0.0

WindowAgg (cost=25,745.51..25,747.96 rows=98 width=40) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=25,745.51..25,745.76 rows=98 width=72) (actual rows= loops=)

  • Sort Key: a."CREATED_DATE", a.type
7. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=25,740.31..25,742.27 rows=98 width=72) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

HashAggregate (cost=25,740.31..25,741.29 rows=98 width=154) (actual rows= loops=)

  • Group Key: a_1."ID", cgdg."CREATED_DATE", a_1."CUSTOMER_ID", ('master'::text)
9. 0.000 0.000 ↓ 0.0

Append (cost=847.47..25,739.33 rows=98 width=154) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=847.47..1,320.59 rows=2 width=72) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=847.05..1,318.31 rows=2 width=56) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Join (cost=846.49..1,296.50 rows=3 width=72) (actual rows= loops=)

  • Hash Cond: ((cgdg."OWNER_ID")::text = (a_1."ID")::text)
13. 0.000 0.000 ↓ 0.0

Seq Scan on "CONTACT_GROUP_DOMAIN_GROUP" cgdg (cost=0.00..413.41 rows=13,941 width=62) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=837.39..837.39 rows=728 width=32) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_fuzzy_search_name on "ACCOUNT" a_1 (cost=0.56..837.39 rows=728 width=32) (actual rows= loops=)

  • Index Cond: (("CUSTOMER_ID" = 'C01rra77w'::text) AND ("NORMAL" = true))
  • Filter: "NORMAL
16. 0.000 0.000 ↓ 0.0

Index Scan using "CONTACT_GROUP_pkey" on "CONTACT_GROUP" cg (cost=0.56..7.26 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (("GROUP_ID")::text = (cgdg."GROUP_ID")::text)
  • Filter: (NOT "DELETED")
17. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_domain_group on "DOMAIN_GROUP" dg (cost=0.42..1.14 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (("ID" = (cgdg."TARGET_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=209.63..736.72 rows=23 width=71) (actual rows= loops=)

  • Join Filter: ((dg_1."ID")::text = (dgm."GROUP_ID")::text)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=209.20..680.46 rows=2 width=50) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=208.64..658.65 rows=3 width=66) (actual rows= loops=)

  • Hash Cond: ((cgdg_1."TARGET_ID")::text = (dg_1."ID")::text)
21. 0.000 0.000 ↓ 0.0

Seq Scan on "CONTACT_GROUP_DOMAIN_GROUP" cgdg_1 (cost=0.00..413.41 rows=13,941 width=40) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=207.95..207.95 rows=55 width=26) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on "DOMAIN_GROUP" dg_1 (cost=4.85..207.95 rows=55 width=26) (actual rows= loops=)

  • Recheck Cond: (("DOMAIN_ID")::text = 'C01rra77w'::text)
24. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on "DOMAIN_GROUP_DOMAIN_ID_DELETED_idx" (cost=0.00..4.83 rows=55 width=0) (actual rows= loops=)

  • Index Cond: (("DOMAIN_ID")::text = 'C01rra77w'::text)
25. 0.000 0.000 ↓ 0.0

Index Scan using "CONTACT_GROUP_pkey" on "CONTACT_GROUP" cg_1 (cost=0.56..7.26 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (("GROUP_ID")::text = (cgdg_1."GROUP_ID")::text)
  • Filter: (NOT "DELETED")
26. 0.000 0.000 ↓ 0.0

Index Scan using index_group_id on "DOMAIN_GROUP_MEMBER" dgm (cost=0.43..25.72 rows=193 width=37) (actual rows= loops=)

  • Index Cond: (("GROUP_ID")::text = (cgdg_1."TARGET_ID")::text)
  • Filter: (("MEMBER_TYPE")::text = 'USER'::text)
27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,232.16..5,706.75 rows=1 width=72) (actual rows= loops=)

  • Join Filter: ((a_3."TARGET_ID")::text = (c."ID")::text)
28. 0.000 0.000 ↓ 0.0

Gather (cost=3,231.74..5,704.50 rows=1 width=72) (actual rows= loops=)

  • Workers Planned: 2
29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,231.74..4,704.40 rows=1 width=72) (actual rows= loops=)

  • Join Filter: ((e."ID")::text <> (a_2."ID")::text)
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,226.28..2,664.55 rows=151 width=98) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,219.23..2,296.15 rows=1 width=56) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,218.68..2,289.91 rows=1 width=94) (actual rows= loops=)

  • Merge Cond: ((e."ID")::text = (a_3."OWNER_ID")::text)
33. 0.000 0.000 ↓ 0.0

Sort (cost=845.64..846.39 rows=303 width=32) (actual rows= loops=)

  • Sort Key: e."ID
34. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using idx_fuzzy_search_name on "ACCOUNT" e (cost=0.56..833.15 rows=303 width=32) (actual rows= loops=)

  • Index Cond: (("CUSTOMER_ID" = 'C01rra77w'::text) AND ("NORMAL" = true))
  • Filter: "NORMAL
35. 0.000 0.000 ↓ 0.0

Sort (cost=1,373.04..1,407.89 rows=13,941 width=62) (actual rows= loops=)

  • Sort Key: a_3."OWNER_ID
36. 0.000 0.000 ↓ 0.0

Seq Scan on "CONTACT_GROUP_DOMAIN_GROUP" a_3 (cost=0.00..413.41 rows=13,941 width=62) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_contact_group on "CONTACT_GROUP" b_1 (cost=0.56..6.24 rows=1 width=38) (actual rows= loops=)

  • Index Cond: (("OWNER_ID" = (a_3."OWNER_ID")::text) AND ("GROUP_ID" = (a_3."GROUP_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")
38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on "DOMAIN_GROUP_MEMBER" b (cost=7.05..366.40 rows=200 width=42) (actual rows= loops=)

  • Recheck Cond: (("GROUP_ID")::text = (a_3."TARGET_ID")::text)
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_group_id (cost=0.00..7.00 rows=200 width=0) (actual rows= loops=)

  • Index Cond: (("GROUP_ID")::text = (a_3."TARGET_ID")::text)
40. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on "ACCOUNT" a_2 (cost=5.46..13.50 rows=1 width=32) (actual rows= loops=)

  • Recheck Cond: (((b."MEMBER_ID")::text = ("ID")::text) OR (((b."MEMBER_ID")::text = ("CUSTOMER_ID")::text) AND (("CUSTOMER_ID")::text = 'C01rra77w'::text) AND "NORMAL"))
  • Filter: ("NORMAL" AND (("CUSTOMER_ID")::text = 'C01rra77w'::text) AND ((((b."MEMBER_ID")::text = ("ID")::text) AND ((b."MEMBER_TYPE")::text = 'USER'::text)) OR (((b."MEMBER_ID")::text = ("CUSTOMER_ID")::text) AND ((b."MEMBER_TYPE")::text = 'CUSTOMER'::text))))
41. 0.000 0.000 ↓ 0.0

BitmapOr (cost=5.46..5.46 rows=2 width=0) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on "ACCOUNT_pkey" (cost=0.00..2.61 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((b."MEMBER_ID")::text = ("ID")::text)
43. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_fuzzy_search_name (cost=0.00..2.85 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (((b."MEMBER_ID")::text = ("CUSTOMER_ID")::text) AND (("CUSTOMER_ID")::text = 'C01rra77w'::text) AND ("NORMAL" = true))
44. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_domain_group on "DOMAIN_GROUP" c (cost=0.42..2.23 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (("ID" = (b."GROUP_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")
45. 0.000 0.000 ↓ 0.0

Gather (cost=1,847.05..7,908.20 rows=35 width=72) (actual rows= loops=)

  • Workers Planned: 2
46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=847.05..6,904.70 rows=15 width=72) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash Join (cost=846.49..6,861.89 rows=25 width=78) (actual rows= loops=)

  • Hash Cond: ((a_4."OWNER_ID")::text = (c_1."ID")::text)
48. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on "CONTACT_GROUP_ACCOUNT" a_4 (cost=0.00..5,735.04 rows=106,804 width=46) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=837.39..837.39 rows=728 width=32) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_fuzzy_search_name on "ACCOUNT" c_1 (cost=0.56..837.39 rows=728 width=32) (actual rows= loops=)

  • Index Cond: (("CUSTOMER_ID" = 'C01rra77w'::text) AND ("NORMAL" = true))
  • Filter: "NORMAL
51. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_contact_group on "CONTACT_GROUP" b_2 (cost=0.56..1.71 rows=1 width=38) (actual rows= loops=)

  • Index Cond: (("OWNER_ID" = (a_4."OWNER_ID")::text) AND ("GROUP_ID" = (a_4."GROUP_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")
52. 0.000 0.000 ↓ 0.0

Gather (cost=1,847.05..7,908.20 rows=35 width=72) (actual rows= loops=)

  • Workers Planned: 2
53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=847.05..6,904.70 rows=15 width=72) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash Join (cost=846.49..6,861.89 rows=25 width=100) (actual rows= loops=)

  • Hash Cond: ((a_5."OWNER_ID")::text = (c_2."ID")::text)
55. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on "CONTACT_GROUP_ACCOUNT" a_5 (cost=0.00..5,735.04 rows=106,804 width=68) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=837.39..837.39 rows=728 width=32) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_fuzzy_search_name on "ACCOUNT" c_2 (cost=0.56..837.39 rows=728 width=32) (actual rows= loops=)

  • Index Cond: (("CUSTOMER_ID" = 'C01rra77w'::text) AND ("NORMAL" = true))
  • Filter: "NORMAL
58. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_contact_group on "CONTACT_GROUP" b_3 (cost=0.56..1.71 rows=1 width=38) (actual rows= loops=)

  • Index Cond: (("OWNER_ID" = (a_5."OWNER_ID")::text) AND ("GROUP_ID" = (a_5."GROUP_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")
59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=847.05..1,078.95 rows=1 width=72) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash Join (cost=846.49..1,065.39 rows=2 width=78) (actual rows= loops=)

  • Hash Cond: ((a_6."OWNER_ID")::text = (c_3."ID")::text)
61. 0.000 0.000 ↓ 0.0

Seq Scan on "CONTACT_GROUP_ACCOUNT_TEMP" a_6 (cost=0.00..201.87 rows=6,487 width=46) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=837.39..837.39 rows=728 width=32) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_fuzzy_search_name on "ACCOUNT" c_3 (cost=0.56..837.39 rows=728 width=32) (actual rows= loops=)

  • Index Cond: (("CUSTOMER_ID" = 'C01rra77w'::text) AND ("NORMAL" = true))
  • Filter: "NORMAL
64. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_contact_group on "CONTACT_GROUP" b_4 (cost=0.56..6.78 rows=1 width=38) (actual rows= loops=)

  • Index Cond: (("OWNER_ID" = (a_6."OWNER_ID")::text) AND ("GROUP_ID" = (a_6."GROUP_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")
65. 0.000 0.000 ↓ 0.0

Nested Loop (cost=847.05..1,078.95 rows=1 width=72) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash Join (cost=846.49..1,065.39 rows=2 width=100) (actual rows= loops=)

  • Hash Cond: ((a_7."OWNER_ID")::text = (c_4."ID")::text)
67. 0.000 0.000 ↓ 0.0

Seq Scan on "CONTACT_GROUP_ACCOUNT_TEMP" a_7 (cost=0.00..201.87 rows=6,487 width=68) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash (cost=837.39..837.39 rows=728 width=32) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_fuzzy_search_name on "ACCOUNT" c_4 (cost=0.56..837.39 rows=728 width=32) (actual rows= loops=)

  • Index Cond: (("CUSTOMER_ID" = 'C01rra77w'::text) AND ("NORMAL" = true))
  • Filter: "NORMAL
70. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_existing_contact_group on "CONTACT_GROUP" b_5 (cost=0.56..6.78 rows=1 width=38) (actual rows= loops=)

  • Index Cond: (("OWNER_ID" = (a_7."OWNER_ID")::text) AND ("GROUP_ID" = (a_7."GROUP_ID")::text) AND ("DELETED" = false))
  • Filter: (NOT "DELETED")