explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bDLF

Settings
# exclusive inclusive rows x rows loops node
1. 1,200.457 6,206.645 ↓ 4.1 239,604 1

Merge Left Join (cost=240,589.18..247,779.92 rows=58,378 width=1,139) (actual time=3,503.463..6,206.645 rows=239,604 loops=1)

  • Merge Cond: (t3.cust_id = p_4.cust_id)
2. 214.651 4,311.960 ↓ 4.1 239,604 1

Merge Left Join (cost=187,057.59..188,362.03 rows=58,378 width=1,107) (actual time=2,925.197..4,311.960 rows=239,604 loops=1)

  • Merge Cond: (t3.cust_id = p_3.cust_id)
3. 163.743 3,387.950 ↓ 4.1 239,604 1

Merge Left Join (cost=133,525.99..134,635.99 rows=58,378 width=1,075) (actual time=2,323.544..3,387.950 rows=239,604 loops=1)

  • Merge Cond: (t3.cust_id = p_2.cust_id)
4. 158.417 3,210.200 ↓ 4.1 239,604 1

Merge Left Join (cost=133,071.63..134,035.03 rows=58,378 width=1,043) (actual time=2,312.505..3,210.200 rows=239,604 loops=1)

  • Merge Cond: (t3.cust_id = p_1.cust_id)
5. 210.391 3,034.509 ↓ 4.1 239,604 1

Merge Left Join (cost=120,735.93..121,552.71 rows=58,378 width=1,011) (actual time=2,298.040..3,034.509 rows=239,604 loops=1)

  • Merge Cond: (t3.cust_id = p.cust_id)
6. 168.781 2,583.031 ↓ 4.1 239,604 1

Merge Left Join (cost=82,600.50..82,892.43 rows=58,378 width=979) (actual time=2,163.386..2,583.031 rows=239,604 loops=1)

  • Merge Cond: (t3.cust_id = c.cust_id)
7. 674.144 2,414.208 ↓ 4.1 239,604 1

Sort (cost=82,574.80..82,720.75 rows=58,378 width=912) (actual time=2,163.340..2,414.208 rows=239,604 loops=1)

  • Sort Key: t3.cust_id
  • Sort Method: external merge Disk: 42376kB
8. 138.986 1,740.064 ↓ 4.1 239,604 1

Hash Left Join (cost=18,670.96..54,604.77 rows=58,378 width=912) (actual time=245.842..1,740.064 rows=239,604 loops=1)

  • Hash Cond: (t3.cust_prvcy_tp_id = prvcytp.gnl_tp_id)
9. 346.218 1,600.775 ↓ 4.1 239,604 1

Hash Left Join (cost=18,654.34..54,335.45 rows=58,378 width=911) (actual time=245.530..1,600.775 rows=239,604 loops=1)

  • Hash Cond: (t1.party_id = t6.party_id)
10. 223.057 1,157.503 ↓ 4.1 239,604 1

Hash Join (cost=4,122.39..25,451.80 rows=58,378 width=888) (actual time=148.395..1,157.503 rows=239,604 loops=1)

  • Hash Cond: ((t1.party_tp_id = t2.gnl_tp_id) AND ((lang.lang)::text = (t2lang.lang)::text))
11. 150.116 932.487 ↓ 3.7 239,604 1

Hash Join (cost=4,053.28..23,996.71 rows=64,176 width=872) (actual time=146.426..932.487 rows=239,604 loops=1)

  • Hash Cond: (t4.gnl_st_id = lang.gnl_st_id)
12. 114.911 781.758 ↓ 3.3 79,868 1

Nested Loop (cost=4,028.18..23,178.38 rows=24,235 width=867) (actual time=145.805..781.758 rows=79,868 loops=1)

13. 197.858 427.243 ↓ 3.2 79,868 1

Hash Join (cost=4,027.89..13,284.95 rows=25,074 width=344) (actual time=145.786..427.243 rows=79,868 loops=1)

  • Hash Cond: (t5.party_role_id = t3.party_role_id)
14. 83.698 83.698 ↓ 1.0 241,971 1

Seq Scan on party_role t5 (cost=0.00..4,605.87 rows=240,387 width=11) (actual time=0.002..83.698 rows=241,971 loops=1)

15. 49.993 145.687 ↓ 3.2 79,868 1

Hash (cost=2,563.46..2,563.46 rows=25,074 width=345) (actual time=145.687..145.687 rows=79,868 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 2240kB
16. 71.171 95.694 ↓ 3.2 79,868 1

Hash Join (cost=10.39..2,563.46 rows=25,074 width=345) (actual time=0.136..95.694 rows=79,868 loops=1)

  • Hash Cond: (t3.st_id = t4.gnl_st_id)
17. 24.401 24.401 ↑ 1.0 79,988 1

Seq Scan on cust t3 (cost=0.00..1,992.15 rows=82,715 width=334) (actual time=0.008..24.401 rows=79,988 loops=1)

18. 0.042 0.122 ↓ 1.1 95 1

Hash (cost=9.30..9.30 rows=87 width=11) (actual time=0.122..0.122 rows=95 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
19. 0.080 0.080 ↓ 1.1 95 1

Seq Scan on gnl_st t4 (cost=0.00..9.30 rows=87 width=11) (actual time=0.007..0.080 rows=95 loops=1)

  • Filter: (((shrt_code)::text = 'ACTV'::text) OR ((shrt_code)::text = 'PASS'::text))
  • Rows Removed by Filter: 192
20. 239.604 239.604 ↑ 1.0 1 79,868

Index Scan using pk_party on party t1 (cost=0.29..0.38 rows=1 width=529) (actual time=0.003..0.003 rows=1 loops=79,868)

  • Index Cond: (party_id = t3.party_id)
21. 0.361 0.613 ↑ 1.0 760 1

Hash (cost=15.60..15.60 rows=760 width=20) (actual time=0.613..0.613 rows=760 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
22. 0.252 0.252 ↑ 1.0 760 1

Seq Scan on gnl_st_lang lang (cost=0.00..15.60 rows=760 width=20) (actual time=0.002..0.252 rows=760 loops=1)

23. 0.531 1.959 ↑ 1.0 1,045 1

Hash (cost=53.44..53.44 rows=1,045 width=34) (actual time=1.959..1.959 rows=1,045 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
24. 0.892 1.428 ↑ 1.0 1,045 1

Hash Join (cost=16.62..53.44 rows=1,045 width=34) (actual time=0.281..1.428 rows=1,045 loops=1)

  • Hash Cond: (t2lang.gnl_tp_id = t2.gnl_tp_id)
25. 0.264 0.264 ↑ 1.0 1,045 1

Seq Scan on gnl_tp_lang t2lang (cost=0.00..22.45 rows=1,045 width=21) (actual time=0.003..0.264 rows=1,045 loops=1)

26. 0.147 0.272 ↑ 1.0 383 1

Hash (cost=11.83..11.83 rows=383 width=13) (actual time=0.272..0.272 rows=383 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
27. 0.125 0.125 ↑ 1.0 383 1

Seq Scan on gnl_tp t2 (cost=0.00..11.83 rows=383 width=13) (actual time=0.001..0.125 rows=383 loops=1)

28. 38.654 97.054 ↓ 1.0 80,459 1

Hash (cost=12,979.09..12,979.09 rows=80,309 width=29) (actual time=97.054..97.054 rows=80,459 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2946kB
29. 58.400 58.400 ↓ 1.0 80,459 1

Seq Scan on apl_user t6 (cost=0.00..12,979.09 rows=80,309 width=29) (actual time=0.007..58.400 rows=80,459 loops=1)

30. 0.154 0.303 ↑ 1.0 383 1

Hash (cost=11.83..11.83 rows=383 width=13) (actual time=0.303..0.303 rows=383 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
31. 0.149 0.149 ↑ 1.0 383 1

Seq Scan on gnl_tp prvcytp (cost=0.00..11.83 rows=383 width=13) (actual time=0.003..0.149 rows=383 loops=1)

32. 0.008 0.042 ↓ 0.0 0 1

Sort (cost=25.70..25.71 rows=3 width=74) (actual time=0.042..0.042 rows=0 loops=1)

  • Sort Key: c.cust_id
  • Sort Method: quicksort Memory: 25kB
33. 0.000 0.034 ↓ 0.0 0 1

Nested Loop (cost=4.59..25.68 rows=3 width=74) (actual time=0.034..0.034 rows=0 loops=1)

34. 0.002 0.034 ↓ 0.0 0 1

Nested Loop (cost=0.29..13.09 rows=1 width=12) (actual time=0.034..0.034 rows=0 loops=1)

35. 0.032 0.032 ↓ 0.0 0 1

Seq Scan on media_rel mr (cost=0.00..4.77 rows=1 width=10) (actual time=0.032..0.032 rows=0 loops=1)

  • Filter: ((data_tp_id = '12'::numeric) AND (prio = '1'::numeric) AND (is_actv = '1'::numeric))
  • Rows Removed by Filter: 101
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_cust on cust c (cost=0.29..8.31 rows=1 width=7) (never executed)

  • Index Cond: (cust_id = mr.row_id)
  • Heap Fetches: 0
37. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on media_lang m (cost=4.30..12.56 rows=3 width=72) (never executed)

  • Recheck Cond: (media_id = mr.media_id)
  • Filter: (is_actv = '1'::numeric)
38. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_media_lang_media_id (cost=0.00..4.29 rows=3 width=0) (never executed)

  • Index Cond: (media_id = mr.media_id)
39. 38.736 241.087 ↓ 46.7 115,924 1

Materialize (cost=38,135.43..38,490.63 rows=2,481 width=39) (actual time=134.649..241.087 rows=115,924 loops=1)

40. 47.477 202.351 ↓ 15.6 38,642 1

GroupAggregate (cost=38,135.43..38,459.62 rows=2,481 width=15) (actual time=134.646..202.351 rows=38,642 loops=1)

  • Group Key: p.cust_id
41. 58.314 154.874 ↓ 1.1 43,066 1

Sort (cost=38,135.43..38,233.15 rows=39,090 width=15) (actual time=134.633..154.874 rows=43,066 loops=1)

  • Sort Key: p.cust_id
  • Sort Method: external merge Disk: 1176kB
42. 45.050 96.560 ↓ 1.1 43,066 1

Hash Join (cost=2,773.54..35,153.93 rows=39,090 width=15) (actual time=13.225..96.560 rows=43,066 loops=1)

  • Hash Cond: (p.st_id = gs.gnl_st_id)
43. 42.772 51.249 ↑ 1.0 52,132 1

Bitmap Heap Scan on prod p (cost=2,760.83..34,554.65 rows=52,181 width=22) (actual time=12.952..51.249 rows=52,132 loops=1)

  • Recheck Cond: ((ident_shrt_code1)::text = 'MSISDN'::text)
  • Heap Blocks: exact=25842
44. 8.477 8.477 ↓ 1.0 53,040 1

Bitmap Index Scan on prod_ident_shrt_code1_idx (cost=0.00..2,747.78 rows=52,181 width=0) (actual time=8.477..8.477 rows=53,040 loops=1)

  • Index Cond: ((ident_shrt_code1)::text = 'MSISDN'::text)
45. 0.098 0.261 ↑ 1.0 215 1

Hash (cost=10.02..10.02 rows=215 width=5) (actual time=0.261..0.261 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
46. 0.163 0.163 ↑ 1.0 215 1

Seq Scan on gnl_st gs (cost=0.00..10.02 rows=215 width=5) (actual time=0.010..0.163 rows=215 loops=1)

  • Filter: ((is_actv = '1'::numeric) AND ((shrt_code)::text <> ALL ('{PHANTOM,CNCL,DEL,QUOTE_DEL}'::text[])))
  • Rows Removed by Filter: 72
47. 4.767 17.274 ↓ 198.0 10,888 1

Sort (cost=12,335.70..12,335.84 rows=55 width=39) (actual time=14.460..17.274 rows=10,888 loops=1)

  • Sort Key: p_1.cust_id
  • Sort Method: quicksort Memory: 387kB
48. 5.850 12.507 ↓ 66.0 3,630 1

HashAggregate (cost=12,332.88..12,333.56 rows=55 width=15) (actual time=10.682..12.507 rows=3,630 loops=1)

  • Group Key: p_1.cust_id
49. 5.943 6.657 ↓ 3.3 3,779 1

Bitmap Heap Scan on prod p_1 (cost=238.30..12,327.14 rows=1,148 width=15) (actual time=1.347..6.657 rows=3,779 loops=1)

  • Recheck Cond: ((ident_shrt_code1)::text = 'SERIAL_NUMBER'::text)
  • Filter: (ident_val1 IS NOT NULL)
  • Rows Removed by Filter: 537
  • Heap Blocks: exact=4102
50. 0.714 0.714 ↑ 1.0 4,429 1

Bitmap Index Scan on prod_ident_shrt_code1_idx (cost=0.00..238.01 rows=4,478 width=0) (actual time=0.714..0.714 rows=4,429 loops=1)

  • Index Cond: ((ident_shrt_code1)::text = 'SERIAL_NUMBER'::text)
51. 4.919 14.007 ↓ 198.0 10,888 1

Sort (cost=454.36..454.49 rows=55 width=39) (actual time=11.035..14.007 rows=10,888 loops=1)

  • Sort Key: p_2.cust_id
  • Sort Method: quicksort Memory: 387kB
52. 5.697 9.088 ↓ 66.0 3,630 1

HashAggregate (cost=451.53..452.22 rows=55 width=20) (actual time=7.268..9.088 rows=3,630 loops=1)

  • Group Key: p_2.cust_id
53. 3.391 3.391 ↓ 3.3 3,779 1

Index Scan using prod_ident_shrt_code3_idx on prod p_2 (cost=0.42..445.79 rows=1,148 width=20) (actual time=0.026..3.391 rows=3,779 loops=1)

  • Index Cond: ((ident_shrt_code3)::text = 'MODEM_MAC_ADDR'::text)
  • Filter: (ident_val1 IS NOT NULL)
  • Rows Removed by Filter: 537
54. 39.117 709.359 ↓ 100.2 115,588 1

Materialize (cost=53,531.60..53,569.07 rows=1,153 width=39) (actual time=601.648..709.359 rows=115,588 loops=1)

55. 49.565 670.242 ↓ 33.4 38,530 1

GroupAggregate (cost=53,531.60..53,554.66 rows=1,153 width=13) (actual time=601.646..670.242 rows=38,530 loops=1)

  • Group Key: p_3.cust_id
56. 62.412 620.677 ↓ 37.2 42,940 1

Sort (cost=53,531.60..53,534.48 rows=1,153 width=13) (actual time=601.634..620.677 rows=42,940 loops=1)

  • Sort Key: p_3.cust_id
  • Sort Method: external merge Disk: 1384kB
57. 38.836 558.265 ↓ 37.2 42,940 1

Nested Loop (cost=976.26..53,472.96 rows=1,153 width=13) (actual time=20.837..558.265 rows=42,940 loops=1)

58. 54.897 415.931 ↓ 33.6 51,749 1

Nested Loop (cost=976.11..53,196.07 rows=1,538 width=20) (actual time=20.810..415.931 rows=51,749 loops=1)

59. 37.214 154.038 ↓ 33.6 51,749 1

Hash Join (cost=975.68..52,427.38 rows=1,538 width=12) (actual time=20.795..154.038 rows=51,749 loops=1)

  • Hash Cond: (pcv.st_id = gs2.gnl_st_id)
60. 31.535 116.701 ↓ 6.8 52,704 1

Nested Loop (cost=965.67..52,372.94 rows=7,746 width=19) (actual time=20.656..116.701 rows=52,704 loops=1)

61. 0.011 0.011 ↑ 1.0 1 1

Index Scan using nui_gnl_char_shrt_code on gnl_char gc (cost=0.27..8.29 rows=1 width=5) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (((shrt_code)::text = 'IMSI'::text) AND (is_actv = '1'::numeric))
62. 73.118 85.155 ↓ 1.7 52,704 1

Bitmap Heap Scan on prod_char_val pcv (cost=965.39..52,059.32 rows=30,533 width=24) (actual time=20.640..85.155 rows=52,704 loops=1)

  • Recheck Cond: ((char_id = gc.char_id) AND (val IS NOT NULL))
  • Heap Blocks: exact=44965
63. 12.037 12.037 ↓ 1.7 52,760 1

Bitmap Index Scan on idx_prod_char_val_v02 (cost=0.00..957.76 rows=30,533 width=0) (actual time=12.037..12.037 rows=52,760 loops=1)

  • Index Cond: ((char_id = gc.char_id) AND (val IS NOT NULL))
64. 0.024 0.123 ↑ 1.0 57 1

Hash (cost=9.30..9.30 rows=57 width=5) (actual time=0.123..0.123 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
65. 0.099 0.099 ↑ 1.0 57 1

Seq Scan on gnl_st gs2 (cost=0.00..9.30 rows=57 width=5) (actual time=0.011..0.099 rows=57 loops=1)

  • Filter: ((is_actv = '1'::numeric) AND ((shrt_code)::text = 'ACTV'::text))
  • Rows Removed by Filter: 230
66. 206.996 206.996 ↑ 1.0 1 51,749

Index Scan using pk_prod on prod p_3 (cost=0.42..0.49 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=51,749)

  • Index Cond: (prod_id = pcv.prod_id)
67. 103.498 103.498 ↑ 1.0 1 51,749

Index Scan using gnl_st_pkey on gnl_st gs_1 (cost=0.15..0.17 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=51,749)

  • Index Cond: (gnl_st_id = p_3.st_id)
  • Filter: ((shrt_code)::text <> ALL ('{PHANTOM,CNCL,DEL,QUOTE_DEL}'::text[]))
  • Rows Removed by Filter: 0
68. 40.351 694.228 ↓ 100.2 115,588 1

Materialize (cost=53,531.60..53,569.07 rows=1,153 width=39) (actual time=578.152..694.228 rows=115,588 loops=1)

69. 55.788 653.877 ↓ 33.4 38,530 1

GroupAggregate (cost=53,531.60..53,554.66 rows=1,153 width=13) (actual time=578.149..653.877 rows=38,530 loops=1)

  • Group Key: p_4.cust_id
70. 60.374 598.089 ↓ 37.2 42,940 1

Sort (cost=53,531.60..53,534.48 rows=1,153 width=13) (actual time=578.138..598.089 rows=42,940 loops=1)

  • Sort Key: p_4.cust_id
  • Sort Method: external merge Disk: 1592kB
71. 32.380 537.715 ↓ 37.2 42,940 1

Nested Loop (cost=976.26..53,472.96 rows=1,153 width=13) (actual time=19.898..537.715 rows=42,940 loops=1)

72. 46.250 401.837 ↓ 33.6 51,749 1

Nested Loop (cost=976.11..53,196.07 rows=1,538 width=20) (actual time=19.877..401.837 rows=51,749 loops=1)

73. 35.676 148.591 ↓ 33.6 51,749 1

Hash Join (cost=975.68..52,427.38 rows=1,538 width=12) (actual time=19.865..148.591 rows=51,749 loops=1)

  • Hash Cond: (pcv_1.st_id = gs2_1.gnl_st_id)
74. 30.946 112.806 ↓ 6.8 52,702 1

Nested Loop (cost=965.67..52,372.94 rows=7,746 width=19) (actual time=19.745..112.806 rows=52,702 loops=1)

75. 0.011 0.011 ↑ 1.0 1 1

Index Scan using nui_gnl_char_shrt_code on gnl_char gc_1 (cost=0.27..8.29 rows=1 width=5) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (((shrt_code)::text = 'ICCID'::text) AND (is_actv = '1'::numeric))
76. 70.311 81.849 ↓ 1.7 52,702 1

Bitmap Heap Scan on prod_char_val pcv_1 (cost=965.39..52,059.32 rows=30,533 width=24) (actual time=19.730..81.849 rows=52,702 loops=1)

  • Recheck Cond: ((char_id = gc_1.char_id) AND (val IS NOT NULL))
  • Heap Blocks: exact=44956
77. 11.538 11.538 ↓ 1.7 52,758 1

Bitmap Index Scan on idx_prod_char_val_v02 (cost=0.00..957.76 rows=30,533 width=0) (actual time=11.538..11.538 rows=52,758 loops=1)

  • Index Cond: ((char_id = gc_1.char_id) AND (val IS NOT NULL))
78. 0.021 0.109 ↑ 1.0 57 1

Hash (cost=9.30..9.30 rows=57 width=5) (actual time=0.109..0.109 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
79. 0.088 0.088 ↑ 1.0 57 1

Seq Scan on gnl_st gs2_1 (cost=0.00..9.30 rows=57 width=5) (actual time=0.011..0.088 rows=57 loops=1)

  • Filter: ((is_actv = '1'::numeric) AND ((shrt_code)::text = 'ACTV'::text))
  • Rows Removed by Filter: 230
80. 206.996 206.996 ↑ 1.0 1 51,749

Index Scan using pk_prod on prod p_4 (cost=0.42..0.49 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=51,749)

  • Index Cond: (prod_id = pcv_1.prod_id)
81. 103.498 103.498 ↑ 1.0 1 51,749

Index Scan using gnl_st_pkey on gnl_st gs_2 (cost=0.15..0.17 rows=1 width=5) (actual time=0.001..0.002 rows=1 loops=51,749)

  • Index Cond: (gnl_st_id = p_4.st_id)
  • Filter: ((shrt_code)::text <> ALL ('{PHANTOM,CNCL,DEL,QUOTE_DEL}'::text[]))
  • Rows Removed by Filter: 0