explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oxGx

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 47,272.838 ↓ 0.0 0 1

Limit (cost=15,561,887.89..15,561,887.95 rows=25 width=440) (actual time=47,272.838..47,272.838 rows=0 loops=1)

2.          

CTE client_rep

3. 0.003 0.061 ↓ 2.0 2 1

Unique (cost=4.46..4.47 rows=1 width=4) (actual time=0.059..0.061 rows=2 loops=1)

4. 0.016 0.058 ↓ 2.0 2 1

Sort (cost=4.46..4.47 rows=1 width=4) (actual time=0.058..0.058 rows=2 loops=1)

  • Sort Key: client_rep_number_relation.rep_number_id
  • Sort Method: quicksort Memory: 25kB
5. 0.042 0.042 ↓ 2.0 2 1

Index Scan using idx_client_rep_num_relation_client_id on client_rep_number_relation (cost=0.43..4.45 rows=1 width=4) (actual time=0.038..0.042 rows=2 loops=1)

  • Index Cond: (client_id = 52441294)
  • Filter: (rep_number_id > 0)
6.          

CTE client_details

7. 0.015 0.163 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.43..24.93 rows=1 width=81) (actual time=0.161..0.163 rows=1 loops=1)

8. 0.004 0.125 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.99..24.41 rows=1 width=29) (actual time=0.123..0.125 rows=1 loops=1)

9. 0.004 0.096 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.56..13.79 rows=1 width=25) (actual time=0.095..0.096 rows=1 loops=1)

10. 0.007 0.073 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.00..13.09 rows=1 width=22) (actual time=0.073..0.073 rows=1 loops=1)

11. 0.045 0.045 ↑ 1.0 1 1

Index Scan using pk_client on client c (cost=0.56..4.58 rows=1 width=14) (actual time=0.045..0.045 rows=1 loops=1)

  • Index Cond: (client_id = 52441294)
12. 0.021 0.021 ↓ 0.0 0 1

Index Scan using indx_party_phone_number_party_role_id on party_phone_number ppn (cost=0.43..8.49 rows=3 width=12) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (party_role_id = c.party_role_id)
13. 0.019 0.019 ↓ 0.0 0 1

Index Scan using pk_phone_number on phone_number pn (cost=0.56..0.69 rows=1 width=19) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (phone_number_id = ppn.phone_number_id)
14. 0.025 0.025 ↑ 5.0 1 1

Index Scan using indx_party_address_party_role_id on party_address pa (cost=0.44..10.58 rows=5 width=8) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: (party_role_id = c.party_role_id)
15. 0.023 0.023 ↑ 1.0 1 1

Index Scan using pk_address on address addr (cost=0.44..0.51 rows=1 width=28) (actual time=0.023..0.023 rows=1 loops=1)

  • Index Cond: (pa.address_id = address_id)
16.          

CTE account_search_all

17. 0.341 47,272.800 ↓ 0.0 0 1

HashAggregate (cost=10,703,062.13..10,703,454.16 rows=39,203 width=8) (actual time=47,272.800..47,272.800 rows=0 loops=1)

  • Group Key: "*SELECT* 1".account_id, (1)
18. 0.005 47,272.459 ↓ 0.0 0 1

Append (cost=5,084,314.40..10,702,866.11 rows=39,203 width=8) (actual time=47,272.459..47,272.459 rows=0 loops=1)

19. 0.001 2,768.271 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=5,084,314.40..5,084,318.40 rows=200 width=8) (actual time=2,768.271..2,768.271 rows=0 loops=1)

20. 0.004 2,768.270 ↓ 0.0 0 1

HashAggregate (cost=5,084,314.40..5,084,316.40 rows=200 width=8) (actual time=2,768.270..2,768.270 rows=0 loops=1)

  • Group Key: acc_1.account_id, 1
21. 0.001 2,768.266 ↓ 0.0 0 1

Hash Left Join (cost=3,326,849.92..4,995,190.37 rows=17,824,806 width=8) (actual time=2,768.265..2,768.266 rows=0 loops=1)

  • Hash Cond: (pa_1.address_id = a.address_id)
  • Filter: ((((COALESCE(pn_1.phone_number, ''::character varying))::text <> ''::text) AND (hashed SubPlan 6)) OR (((COALESCE(a.address_line1, ''::character varying))::text <> ''::text) AND ((COALESCE(a.zipcode, ''::character varying))::text <> ''::text) AND (hashed SubPlan 7)))
22. 1.933 2,768.265 ↓ 0.0 0 1

Hash Right Join (cost=2,388,330.35..3,598,202.92 rows=23,886,037 width=19) (actual time=2,768.265..2,768.265 rows=0 loops=1)

  • Hash Cond: (pa_1.party_role_id = aif.party_role_id)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on party_address pa_1 (cost=0.00..592,050.03 rows=17,593,703 width=8) (never executed)

24. 0.001 2,766.332 ↓ 0.0 0 1

Hash (cost=2,251,778.22..2,251,778.22 rows=7,437,690 width=19) (actual time=2,766.332..2,766.332 rows=0 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 8192kB
25. 0.001 2,766.331 ↓ 0.0 0 1

Hash Left Join (cost=1,288,647.53..2,251,778.22 rows=7,437,690 width=19) (actual time=2,766.331..2,766.331 rows=0 loops=1)

  • Hash Cond: (ppn_1.phone_number_id = pn_1.phone_number_id)
26. 3.607 2,766.330 ↓ 0.0 0 1

Hash Right Join (cost=441,993.11..1,236,084.86 rows=7,437,690 width=16) (actual time=2,766.330..2,766.330 rows=0 loops=1)

  • Hash Cond: (ppn_1.party_role_id = aif.party_role_id)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on party_phone_number ppn_1 (cost=0.00..526,689.91 rows=10,283,191 width=12) (never executed)

28. 0.003 2,762.723 ↓ 0.0 0 1

Hash (cost=376,983.76..376,983.76 rows=3,962,428 width=8) (actual time=2,762.723..2,762.723 rows=0 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 16384kB
29. 52.425 2,762.720 ↓ 0.0 0 1

Hash Join (cost=310,185.21..376,983.76 rows=3,962,428 width=8) (actual time=2,762.720..2,762.720 rows=0 loops=1)

  • Hash Cond: (acc_1.aif_id = aif.aif_id)
30. 0.018 0.515 ↑ 52,832.4 75 1

Nested Loop (cost=0.02..4,039.19 rows=3,962,428 width=8) (actual time=0.227..0.515 rows=75 loops=1)

31. 0.011 0.075 ↓ 2.0 2 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.073..0.075 rows=2 loops=1)

  • Group Key: client_rep.rep_number_id
32. 0.064 0.064 ↓ 2.0 2 1

CTE Scan on client_rep (cost=0.00..0.02 rows=1 width=4) (actual time=0.062..0.064 rows=2 loops=1)

33. 0.056 0.422 ↑ 44.8 38 2

Append (cost=0.00..4,022.14 rows=1,702 width=12) (actual time=0.071..0.211 rows=38 loops=2)

34. 0.016 0.016 ↓ 0.0 0 2

Seq Scan on account_bd_min_1 acc_1 (cost=0.00..1.02 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=2)

  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text) AND (client_rep.rep_number_id = rep_number_id))
  • Rows Removed by Filter: 1
35. 0.204 0.238 ↑ 19.2 38 2

Bitmap Heap Scan on account_bd_1 acc_2 (cost=12.28..1,484.74 rows=728 width=12) (actual time=0.026..0.119 rows=38 loops=2)

  • Recheck Cond: (rep_number_id = client_rep.rep_number_id)
  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=59
36. 0.034 0.034 ↑ 19.9 38 2

Bitmap Index Scan on indx_account_bd_1_rep_number_id (cost=0.00..12.09 rows=755 width=0) (actual time=0.017..0.017 rows=38 loops=2)

  • Index Cond: (rep_number_id = client_rep.rep_number_id)
37. 0.014 0.014 ↓ 0.0 0 2

Index Scan using indx_account_bd_2_4_rep_number_id on account_bd_2_4 acc_3 (cost=0.14..4.16 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep.rep_number_id)
  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text))
38. 0.006 0.036 ↓ 0.0 0 2

Bitmap Heap Scan on account_bd_4 acc_4 (cost=11.64..1,309.73 rows=659 width=12) (actual time=0.018..0.018 rows=0 loops=2)

  • Recheck Cond: (rep_number_id = client_rep.rep_number_id)
  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text))
39. 0.030 0.030 ↓ 0.0 0 2

Bitmap Index Scan on indx_account_bd_4_rep_number_id (cost=0.00..11.48 rows=673 width=0) (actual time=0.015..0.015 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep.rep_number_id)
40. 0.008 0.008 ↓ 0.0 0 2

Index Scan using indx_account_bd_5_6_rep_number_id on account_bd_5_6 acc_5 (cost=0.14..4.16 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep.rep_number_id)
  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text))
41. 0.004 0.028 ↓ 0.0 0 2

Bitmap Heap Scan on account_bd_7 acc_6 (cost=3.75..337.59 rows=156 width=12) (actual time=0.014..0.014 rows=0 loops=2)

  • Recheck Cond: (rep_number_id = client_rep.rep_number_id)
  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text))
42. 0.024 0.024 ↓ 0.0 0 2

Bitmap Index Scan on indx_account_bd_7_rep_number_id (cost=0.00..3.71 rows=171 width=0) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep.rep_number_id)
43. 0.004 0.026 ↓ 0.0 0 2

Bitmap Heap Scan on account_bd_8_max acc_7 (cost=7.89..880.73 rows=156 width=12) (actual time=0.013..0.013 rows=0 loops=2)

  • Recheck Cond: (rep_number_id = client_rep.rep_number_id)
  • Filter: ((client_id <> 52441294) AND ((view_status)::text <> 'SUPPRESSED'::text))
44. 0.022 0.022 ↓ 0.0 0 2

Bitmap Index Scan on indx_account_bd_8_max_rep_number_id (cost=0.00..7.86 rows=457 width=0) (actual time=0.011..0.011 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep.rep_number_id)
45. 1,075.927 2,709.780 ↑ 1.0 5,471,028 1

Hash (cost=220,305.60..220,305.60 rows=5,478,367 width=8) (actual time=2,709.780..2,709.780 rows=5,471,028 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 43097kB
46. 1,633.853 1,633.853 ↑ 1.0 5,471,028 1

Seq Scan on aif (cost=0.00..220,305.60 rows=5,478,367 width=8) (actual time=0.018..1,633.853 rows=5,471,028 loops=1)

  • Filter: (aif_id > 0)
  • Rows Removed by Filter: 1
47. 0.000 0.000 ↓ 0.0 0

Hash (cost=605,761.52..605,761.52 rows=13,120,952 width=19) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on phone_number pn_1 (cost=0.00..605,761.52 rows=13,120,952 width=19) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Hash (cost=610,827.67..610,827.67 rows=16,947,267 width=28) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Seq Scan on address a (cost=0.00..610,827.67 rows=16,947,267 width=28) (never executed)

51.          

SubPlan (forHash Left Join)

52. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.04 rows=1 width=32) (never executed)

  • Group Key: lower((client_details_3.phone_number)::text)
53. 0.000 0.000 ↓ 0.0 0

CTE Scan on client_details client_details_3 (cost=0.00..0.02 rows=1 width=32) (never executed)

54. 0.000 0.000 ↓ 0.0 0

CTE Scan on client_details client_details_4 (cost=0.00..0.03 rows=1 width=32) (never executed)

55. 0.002 44,504.135 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=5,615,906.15..5,615,910.15 rows=200 width=8) (actual time=44,504.135..44,504.135 rows=0 loops=1)

56. 0.007 44,504.133 ↓ 0.0 0 1

HashAggregate (cost=5,615,906.15..5,615,908.15 rows=200 width=8) (actual time=44,504.133..44,504.133 rows=0 loops=1)

  • Group Key: acc_8.account_id, 1
57. 2,683.689 44,504.126 ↓ 0.0 0 1

Hash Left Join (cost=4,318,274.10..5,595,805.79 rows=4,020,072 width=8) (actual time=44,504.126..44,504.126 rows=0 loops=1)

  • Hash Cond: (pa_2.address_id = a_1.address_id)
  • Filter: ((((COALESCE(c_1.last_name, ''::character varying))::text <> ''::text) AND (hashed SubPlan 3)) OR (((COALESCE(pn_2.phone_number, ''::character varying))::text <> ''::text) AND (hashed SubPlan 4)) OR (((COALESCE(a_1.address_line1, ''::character varying))::text <> ''::text) AND ((COALESCE(a_1.zipcode, ''::character varying))::text <> ''::text) AND (hashed SubPlan 5)))
  • Rows Removed by Filter: 75
58. 189.061 32,754.174 ↑ 61,434.7 75 1

Hash Left Join (cost=3,379,754.49..4,466,344.23 rows=4,607,599 width=29) (actual time=25,840.144..32,754.174 rows=75 loops=1)

  • Hash Cond: (ppn_2.phone_number_id = pn_2.phone_number_id)
59. 5,158.728 25,479.973 ↑ 61,434.7 75 1

Hash Right Join (cost=2,533,100.07..3,467,717.86 rows=4,607,599 width=26) (actual time=18,748.870..25,479.973 rows=75 loops=1)

  • Hash Cond: (pa_2.party_role_id = c_1.party_role_id)
60. 3,687.891 3,687.891 ↑ 1.0 17,592,680 1

Seq Scan on party_address pa_2 (cost=0.00..592,050.03 rows=17,593,703 width=8) (actual time=0.029..3,687.891 rows=17,592,680 loops=1)

61. 0.488 16,633.354 ↑ 52,832.4 75 1

Hash (cost=2,456,482.72..2,456,482.72 rows=3,962,428 width=26) (actual time=16,633.354..16,633.354 rows=75 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 8193kB
62. 3,171.819 16,632.866 ↑ 52,832.4 75 1

Hash Right Join (cost=1,702,098.31..2,456,482.72 rows=3,962,428 width=26) (actual time=15,618.034..16,632.866 rows=75 loops=1)

  • Hash Cond: (ppn_2.party_role_id = c_1.party_role_id)
63. 2,730.455 2,730.455 ↓ 1.0 10,325,240 1

Seq Scan on party_phone_number ppn_2 (cost=0.00..526,689.91 rows=10,283,191 width=12) (actual time=0.027..2,730.455 rows=10,325,240 loops=1)

64. 0.221 10,730.592 ↑ 52,832.4 75 1

Hash (cost=1,629,349.96..1,629,349.96 rows=3,962,428 width=18) (actual time=10,730.592..10,730.592 rows=75 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 8193kB
65. 2,564.081 10,730.371 ↑ 52,832.4 75 1

Hash Join (cost=1,495,297.42..1,629,349.96 rows=3,962,428 width=18) (actual time=8,171.835..10,730.371 rows=75 loops=1)

  • Hash Cond: (acc_8.client_id = c_1.client_id)
66. 0.027 0.565 ↑ 52,832.4 75 1

Nested Loop (cost=0.02..4,039.19 rows=3,962,428 width=8) (actual time=0.205..0.565 rows=75 loops=1)

67. 0.014 0.018 ↓ 2.0 2 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.016..0.018 rows=2 loops=1)

  • Group Key: client_rep_1.rep_number_id
68. 0.004 0.004 ↓ 2.0 2 1

CTE Scan on client_rep client_rep_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.004 rows=2 loops=1)

69. 0.060 0.520 ↑ 44.8 38 2

Append (cost=0.00..4,022.14 rows=1,702 width=12) (actual time=0.087..0.260 rows=38 loops=2)

70. 0.026 0.026 ↓ 0.0 0 2

Seq Scan on account_bd_min_1 acc_8 (cost=0.00..1.02 rows=1 width=12) (actual time=0.013..0.013 rows=0 loops=2)

  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294) AND (client_rep_1.rep_number_id = rep_number_id))
  • Rows Removed by Filter: 1
71. 0.260 0.300 ↑ 19.2 38 2

Bitmap Heap Scan on account_bd_1 acc_9 (cost=12.28..1,484.74 rows=728 width=12) (actual time=0.031..0.150 rows=38 loops=2)

  • Recheck Cond: (rep_number_id = client_rep_1.rep_number_id)
  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=59
72. 0.040 0.040 ↑ 19.9 38 2

Bitmap Index Scan on indx_account_bd_1_rep_number_id (cost=0.00..12.09 rows=755 width=0) (actual time=0.020..0.020 rows=38 loops=2)

  • Index Cond: (rep_number_id = client_rep_1.rep_number_id)
73. 0.018 0.018 ↓ 0.0 0 2

Index Scan using indx_account_bd_2_4_rep_number_id on account_bd_2_4 acc_10 (cost=0.14..4.16 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep_1.rep_number_id)
  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294))
74. 0.006 0.038 ↓ 0.0 0 2

Bitmap Heap Scan on account_bd_4 acc_11 (cost=11.64..1,309.73 rows=659 width=12) (actual time=0.019..0.019 rows=0 loops=2)

  • Recheck Cond: (rep_number_id = client_rep_1.rep_number_id)
  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294))
75. 0.032 0.032 ↓ 0.0 0 2

Bitmap Index Scan on indx_account_bd_4_rep_number_id (cost=0.00..11.48 rows=673 width=0) (actual time=0.016..0.016 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep_1.rep_number_id)
76. 0.010 0.010 ↓ 0.0 0 2

Index Scan using indx_account_bd_5_6_rep_number_id on account_bd_5_6 acc_12 (cost=0.14..4.16 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep_1.rep_number_id)
  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294))
77. 0.006 0.030 ↓ 0.0 0 2

Bitmap Heap Scan on account_bd_7 acc_13 (cost=3.75..337.59 rows=156 width=12) (actual time=0.015..0.015 rows=0 loops=2)

  • Recheck Cond: (rep_number_id = client_rep_1.rep_number_id)
  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294))
78. 0.024 0.024 ↓ 0.0 0 2

Bitmap Index Scan on indx_account_bd_7_rep_number_id (cost=0.00..3.71 rows=171 width=0) (actual time=0.012..0.012 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep_1.rep_number_id)
79. 0.016 0.038 ↓ 0.0 0 2

Bitmap Heap Scan on account_bd_8_max acc_14 (cost=7.89..880.73 rows=156 width=12) (actual time=0.019..0.019 rows=0 loops=2)

  • Recheck Cond: (rep_number_id = client_rep_1.rep_number_id)
  • Filter: (((view_status)::text <> 'SUPPRESSED'::text) AND (client_id <> 52441294))
80. 0.022 0.022 ↓ 0.0 0 2

Bitmap Index Scan on indx_account_bd_8_max_rep_number_id (cost=0.00..7.86 rows=457 width=0) (actual time=0.011..0.011 rows=0 loops=2)

  • Index Cond: (rep_number_id = client_rep_1.rep_number_id)
81. 2,951.398 8,165.725 ↑ 1.2 12,394,057 1

Hash (cost=1,217,516.20..1,217,516.20 rows=15,130,176 width=18) (actual time=8,165.725..8,165.725 rows=12,394,057 loops=1)

  • Buckets: 1048576 Batches: 16 Memory Usage: 46670kB
82. 5,214.327 5,214.327 ↑ 1.2 12,394,057 1

Seq Scan on client c_1 (cost=0.00..1,217,516.20 rows=15,130,176 width=18) (actual time=0.018..5,214.327 rows=12,394,057 loops=1)

  • Filter: (client_id > 0)
  • Rows Removed by Filter: 1
83. 3,596.514 7,085.140 ↓ 1.0 13,159,555 1

Hash (cost=605,761.52..605,761.52 rows=13,120,952 width=19) (actual time=7,085.140..7,085.140 rows=13,159,555 loops=1)

  • Buckets: 1048576 Batches: 16 Memory Usage: 47436kB
84. 3,488.626 3,488.626 ↓ 1.0 13,159,555 1

Seq Scan on phone_number pn_2 (cost=0.00..605,761.52 rows=13,120,952 width=19) (actual time=0.014..3,488.626 rows=13,159,555 loops=1)

85. 4,435.192 9,066.055 ↓ 1.0 16,947,711 1

Hash (cost=610,827.67..610,827.67 rows=16,947,267 width=28) (actual time=9,066.055..9,066.055 rows=16,947,711 loops=1)

  • Buckets: 1048576 Batches: 32 Memory Usage: 39243kB
86. 4,630.863 4,630.863 ↓ 1.0 16,947,711 1

Seq Scan on address a_1 (cost=0.00..610,827.67 rows=16,947,267 width=28) (actual time=0.021..4,630.863 rows=16,947,711 loops=1)

87.          

SubPlan (forHash Left Join)

88. 0.008 0.193 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.04 rows=1 width=32) (actual time=0.193..0.193 rows=1 loops=1)

  • Group Key: lower((client_details.last_name)::text)
89. 0.185 0.185 ↑ 1.0 1 1

CTE Scan on client_details (cost=0.00..0.02 rows=1 width=32) (actual time=0.183..0.185 rows=1 loops=1)

90. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.04 rows=1 width=32) (never executed)

  • Group Key: lower((client_details_1.phone_number)::text)
91. 0.000 0.000 ↓ 0.0 0

CTE Scan on client_details client_details_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

92. 0.015 0.015 ↑ 1.0 1 1

CTE Scan on client_details client_details_2 (cost=0.00..0.03 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)

93. 0.001 0.048 ↓ 0.0 0 1

Nested Loop (cost=0.55..2,249.53 rows=38,803 width=8) (actual time=0.048..0.048 rows=0 loops=1)

94. 0.047 0.047 ↓ 0.0 0 1

Index Only Scan using unique_haa on household_account_access hhaa (cost=0.43..4.53 rows=120 width=4) (actual time=0.047..0.047 rows=0 loops=1)

  • Index Cond: (client_id = 52441294)
  • Heap Fetches: 0
95. 0.000 0.000 ↓ 0.0 0

Append (cost=0.12..18.64 rows=7 width=4) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_min_1_pkey on account_bd_min_1 acc_15 (cost=0.12..0.20 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
97. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_1_pkey on account_bd_1 acc_16 (cost=0.43..4.45 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
98. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_2_4_pkey on account_bd_2_4 acc_17 (cost=0.14..0.34 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
99. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_4_pkey on account_bd_4 acc_18 (cost=0.43..4.45 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
100. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_5_6_pkey on account_bd_5_6 acc_19 (cost=0.14..0.34 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
101. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_7_pkey on account_bd_7 acc_20 (cost=0.42..4.43 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
102. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_8_max_pkey on account_bd_8_max acc_21 (cost=0.43..4.43 rows=1 width=4) (never executed)

  • Index Cond: (account_id = hhaa.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
103.          

CTE aif_accounts

104. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..266.48 rows=11 width=8) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_client_aif_client_id on client_aif caif (cost=0.43..7.70 rows=5 width=4) (never executed)

  • Index Cond: (client_id = 52441294)
106. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..51.54 rows=22 width=12) (never executed)

107. 0.000 0.000 ↓ 0.0 0

Seq Scan on account_bd_min_1 acc_22 (cost=0.00..1.01 rows=1 width=12) (never executed)

  • Filter: (caif.aif_id = aif_id)
108. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_account_bd_1_aif_id on account_bd_1 acc_23 (cost=0.43..13.60 rows=6 width=12) (never executed)

  • Index Cond: (aif_id = caif.aif_id)
109. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_account_bd_2_4_aif_id on account_bd_2_4 acc_24 (cost=0.14..2.16 rows=1 width=12) (never executed)

  • Index Cond: (aif_id = caif.aif_id)
110. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_account_bd_4_aif_id on account_bd_4 acc_25 (cost=0.43..11.68 rows=5 width=12) (never executed)

  • Index Cond: (aif_id = caif.aif_id)
111. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_account_bd_5_6_aif_id on account_bd_5_6 acc_26 (cost=0.14..2.16 rows=1 width=12) (never executed)

  • Index Cond: (aif_id = caif.aif_id)
112. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_account_bd_7_aif_id on account_bd_7 acc_27 (cost=0.42..6.45 rows=2 width=12) (never executed)

  • Index Cond: (aif_id = caif.aif_id)
113. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_account_bd_8_max_aif_id on account_bd_8_max acc_28 (cost=0.43..14.49 rows=6 width=12) (never executed)

  • Index Cond: (aif_id = caif.aif_id)
114.          

CTE account_search

115. 0.005 47,272.807 ↓ 0.0 0 1

HashAggregate (cost=980.32..982.32 rows=200 width=12) (actual time=47,272.807..47,272.807 rows=0 loops=1)

  • Group Key: account_search_all.account_id
116. 47,272.802 47,272.802 ↓ 0.0 0 1

CTE Scan on account_search_all (cost=0.25..882.31 rows=19,602 width=8) (actual time=47,272.802..47,272.802 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 10))
117.          

SubPlan (forCTE Scan)

118. 0.000 0.000 ↓ 0.0 0

CTE Scan on aif_accounts (cost=0.00..0.22 rows=11 width=4) (never executed)

119.          

CTE acc_details

120. 0.001 47,272.815 ↓ 0.0 0 1

Hash Left Join (cost=2,031,478.50..2,542,239.35 rows=3,962,443 width=112) (actual time=47,272.815..47,272.815 rows=0 loops=1)

  • Hash Cond: (acc_29.account_id = hhaa_1.account_id)
121. 0.001 47,272.814 ↓ 0.0 0 1

Hash Left Join (cost=2,031,240.55..2,531,597.98 rows=3,962,443 width=105) (actual time=47,272.814..47,272.814 rows=0 loops=1)

  • Hash Cond: (acc_29.account_class_code_map_id = accm.account_class_code_map_id)
122. 0.000 47,272.813 ↓ 0.0 0 1

Hash Left Join (cost=2,031,236.57..2,520,972.18 rows=3,962,443 width=97) (actual time=47,272.813..47,272.813 rows=0 loops=1)

  • Hash Cond: (acc_29.client_id = c_2.client_id)
123. 0.001 47,272.813 ↓ 0.0 0 1

Hash Left Join (cost=573,764.61..863,834.81 rows=3,962,443 width=80) (actual time=47,272.813..47,272.813 rows=0 loops=1)

  • Hash Cond: (acc_29.account_id = am.account_id)
124. 0.001 47,272.812 ↓ 0.0 0 1

Hash Left Join (cost=296,498.41..423,021.95 rows=3,962,443 width=72) (actual time=47,272.812..47,272.812 rows=0 loops=1)

  • Hash Cond: (acc_29.aif_id = aif_1.aif_id)
125. 0.001 47,272.811 ↓ 0.0 0 1

Nested Loop (cost=9.12..1,861.25 rows=3,962,443 width=68) (actual time=47,272.811..47,272.811 rows=0 loops=1)

126. 0.002 47,272.810 ↓ 0.0 0 1

Hash Join (cost=9.00..15.75 rows=100 width=16) (actual time=47,272.809..47,272.810 rows=0 loops=1)

  • Hash Cond: (asea.account_id = account_search.account_id)
127. 47,272.808 47,272.808 ↓ 0.0 0 1

CTE Scan on account_search asea (cost=0.00..4.00 rows=200 width=12) (actual time=47,272.808..47,272.808 rows=0 loops=1)

128. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.50..6.50 rows=200 width=4) (never executed)

129. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=4.50..6.50 rows=200 width=4) (never executed)

  • Group Key: account_search.account_id
130. 0.000 0.000 ↓ 0.0 0

CTE Scan on account_search (cost=0.00..4.00 rows=200 width=4) (never executed)

131. 0.000 0.000 ↓ 0.0 0

Append (cost=0.12..18.38 rows=7 width=60) (never executed)

132. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_min_1_pkey on account_bd_min_1 acc_29 (cost=0.12..0.17 rows=1 width=126) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
133. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_1_pkey on account_bd_1 acc_30 (cost=0.43..4.44 rows=1 width=60) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
134. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_2_4_pkey on account_bd_2_4 acc_31 (cost=0.14..0.27 rows=1 width=126) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
135. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_4_pkey on account_bd_4 acc_32 (cost=0.43..4.43 rows=1 width=60) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
136. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_5_6_pkey on account_bd_5_6 acc_33 (cost=0.14..0.27 rows=1 width=126) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
137. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_7_pkey on account_bd_7 acc_34 (cost=0.42..4.39 rows=1 width=58) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
138. 0.000 0.000 ↓ 0.0 0

Index Scan using account_bd_8_max_pkey on account_bd_8_max acc_35 (cost=0.43..4.41 rows=1 width=60) (never executed)

  • Index Cond: (account_id = asea.account_id)
  • Filter: ((view_status)::text <> 'SUPPRESSED'::text)
139. 0.000 0.000 ↓ 0.0 0

Hash (cost=206,609.68..206,609.68 rows=5,478,368 width=8) (never executed)

140. 0.000 0.000 ↓ 0.0 0

Seq Scan on aif aif_1 (cost=0.00..206,609.68 rows=5,478,368 width=8) (never executed)

141. 0.000 0.000 ↓ 0.0 0

Hash (cost=237,253.87..237,253.87 rows=2,301,787 width=12) (never executed)

142. 0.000 0.000 ↓ 0.0 0

Seq Scan on account_marketvalue am (cost=0.00..237,253.87 rows=2,301,787 width=12) (never executed)

143. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,179,690.76..1,179,690.76 rows=15,130,176 width=21) (never executed)

144. 0.000 0.000 ↓ 0.0 0

Seq Scan on client c_2 (cost=0.00..1,179,690.76 rows=15,130,176 width=21) (never executed)

145. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.88..2.88 rows=88 width=16) (never executed)

146. 0.000 0.000 ↓ 0.0 0

Seq Scan on account_class_code_map accm (cost=0.00..2.88 rows=88 width=16) (never executed)

147. 0.000 0.000 ↓ 0.0 0

Hash (cost=236.45..236.45 rows=120 width=11) (never executed)

148. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on household_account_access hhaa_1 (cost=3.36..236.45 rows=120 width=11) (never executed)

  • Recheck Cond: (client_id = 52441294)
149. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on unique_haa (cost=0.00..3.33 rows=120 width=0) (never executed)

  • Index Cond: (client_id = 52441294)
150.          

CTE parties

151. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=92,457.00..92,459.00 rows=200 width=4) (never executed)

  • Group Key: acc_details.party_role_id
152. 0.000 0.000 ↓ 0.0 0

CTE Scan on acc_details (cost=0.00..89,154.97 rows=1,320,814 width=4) (never executed)

  • Filter: (party_role_id > 0)
153.          

CTE filter_address

154. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=2,427.95..2,438.30 rows=460 width=36) (never executed)

  • Group Key: paddr.party_role_id
155. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,427.95..2,429.10 rows=460 width=70) (never executed)

  • Sort Key: paddr.party_role_id
156. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=16.46..2,407.61 rows=460 width=70) (never executed)

  • Hash Cond: (addr_1.state_id = sm.state_id)
157. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=14.16..2,404.00 rows=460 width=65) (never executed)

  • Hash Cond: (addr_1.country_id = cm.country_id)
158. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.38..2,393.99 rows=460 width=59) (never executed)

159. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.94..2,131.54 rows=460 width=8) (never executed)

160. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=4.50..6.50 rows=200 width=4) (never executed)

  • Group Key: parties.party_role_id
161. 0.000 0.000 ↓ 0.0 0

CTE Scan on parties (cost=0.00..4.00 rows=200 width=4) (never executed)

162. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_party_address_party_role_id on party_address paddr (cost=0.44..10.61 rows=2 width=8) (never executed)

  • Index Cond: (party_role_id = parties.party_role_id)
  • Filter: ((address_id > 0) AND (address_type_id = '2'::numeric) AND ((status)::text = 'ACTIVE'::text))
163. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_address on address addr_1 (cost=0.44..0.57 rows=1 width=59) (never executed)

  • Index Cond: (address_id = paddr.address_id)
164. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.57..5.57 rows=257 width=16) (never executed)

165. 0.000 0.000 ↓ 0.0 0

Seq Scan on lkp_country_master cm (cost=0.00..5.57 rows=257 width=16) (never executed)

166. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.58..1.58 rows=58 width=15) (never executed)

167. 0.000 0.000 ↓ 0.0 0

Seq Scan on lkp_state_master sm (cost=0.00..1.58 rows=58 width=15) (never executed)

168.          

CTE filter_phone

169. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2,063.63..2,069.90 rows=502 width=36) (never executed)

  • Group Key: ppn_3.party_role_id
170. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.49..2,061.12 rows=502 width=15) (never executed)

171. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.93..1,713.00 rows=502 width=12) (never executed)

172. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=4.50..6.50 rows=200 width=4) (never executed)

  • Group Key: parties_1.party_role_id
173. 0.000 0.000 ↓ 0.0 0

CTE Scan on parties parties_1 (cost=0.00..4.00 rows=200 width=4) (never executed)

174. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_party_phone_number_party_role_id on party_phone_number ppn_3 (cost=0.43..8.50 rows=3 width=12) (never executed)

  • Index Cond: (party_role_id = parties_1.party_role_id)
  • Filter: ((phone_number_id > '0'::numeric) AND ((status)::text = 'ACTIVE'::text))
175. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_phone_number on phone_number phn (cost=0.56..0.69 rows=1 width=19) (never executed)

  • Index Cond: (phone_number_id = ppn_3.phone_number_id)
176. 0.012 47,272.836 ↓ 0.0 0 1

Sort (cost=2,217,948.97..2,275,136.93 rows=22,875,184 width=440) (actual time=47,272.836..47,272.836 rows=0 loops=1)

  • Sort Key: acc.account_number
  • Sort Method: quicksort Memory: 25kB
177. 0.004 47,272.824 ↓ 0.0 0 1

WindowAgg (cost=31.27..1,572,427.73 rows=22,875,184 width=440) (actual time=47,272.824..47,272.824 rows=0 loops=1)

178. 0.002 47,272.820 ↓ 0.0 0 1

Hash Left Join (cost=31.27..1,229,299.97 rows=22,875,184 width=664) (actual time=47,272.819..47,272.820 rows=0 loops=1)

  • Hash Cond: (acc.party_role_id = fn.party_role_id)
179. 0.000 47,272.818 ↓ 0.0 0 1

Hash Left Join (cost=14.95..408,146.58 rows=9,113,619 width=632) (actual time=47,272.818..47,272.818 rows=0 loops=1)

  • Hash Cond: (acc.party_role_id = fa.party_role_id)
180. 47,272.818 47,272.818 ↓ 0.0 0 1

CTE Scan on acc_details acc (cost=0.00..79,248.86 rows=3,962,443 width=600) (actual time=47,272.818..47,272.818 rows=0 loops=1)

181. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.20..9.20 rows=460 width=36) (never executed)

182. 0.000 0.000 ↓ 0.0 0

CTE Scan on filter_address fa (cost=0.00..9.20 rows=460 width=36) (never executed)

183. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.04..10.04 rows=502 width=36) (never executed)

184. 0.000 0.000 ↓ 0.0 0

CTE Scan on filter_phone fn (cost=0.00..10.04 rows=502 width=36) (never executed)