explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FkaW

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 28,752.389 ↑ 1.0 10 1

Limit (cost=41,357.93..2,228,166.37 rows=10 width=659) (actual time=5,104.858..28,752.389 rows=10 loops=1)

2. 0.591 28,752.363 ↑ 773,494,897.0 10 1

Nested Loop Left Join (cost=41,357.93..1,691,485,168,780,168.25 rows=7,734,948,970 width=659) (actual time=5,104.856..28,752.363 rows=10 loops=1)

3. 7,883.251 14,932.042 ↑ 773,494,897.0 10 1

Nested Loop Left Join (cost=41,357.78..72,265,139,412.81 rows=7,734,948,970 width=192) (actual time=3,721.522..14,932.042 rows=10 loops=1)

  • Join Filter: (ltrim(rtrim((c.accountnumber)::text)) = ((c1.alcnbr)::character varying)::text)
  • Rows Removed by Join Filter: 12,695,547
4. 0.073 1,933.881 ↑ 121,855.7 10 1

Nested Loop Left Join (cost=2.15..33,590,297,577.77 rows=1,218,557 width=171) (actual time=153.876..1,933.881 rows=10 loops=1)

  • Join Filter: (sd.source_billing_system_housenumber_rgu = cnmocpp.mohnum)
5. 0.063 1,933.698 ↑ 121,855.7 10 1

Nested Loop Left Join (cost=1.72..33,589,685,654.29 rows=1,218,557 width=100) (actual time=153.853..1,933.698 rows=10 loops=1)

6. 0.039 0.815 ↑ 121,855.7 10 1

Nested Loop (cost=1.29..4,985,549.94 rows=1,218,557 width=85) (actual time=0.078..0.815 rows=10 loops=1)

7. 0.067 0.586 ↑ 121,855.7 10 1

Nested Loop (cost=0.86..4,252,057.91 rows=1,218,557 width=81) (actual time=0.060..0.586 rows=10 loops=1)

8. 0.039 0.207 ↑ 117,583.2 24 1

Nested Loop (cost=0.43..2,165,499.32 rows=2,821,997 width=12) (actual time=0.028..0.207 rows=24 loops=1)

9. 0.018 0.018 ↑ 346,884.4 10 1

Seq Scan on hostpf (cost=0.00..172,136.44 rows=3,468,844 width=7) (actual time=0.007..0.018 rows=10 loops=1)

10. 0.150 0.150 ↑ 1.5 2 10

Index Scan using idx1_nu_cnmocpp on cnmocpp (cost=0.43..0.54 rows=3 width=12) (actual time=0.010..0.015 rows=2 loops=10)

  • Index Cond: (mohnum = hostpf.hohnum)
  • Filter: ((mobcrb)::text = 'Y'::text)
  • Rows Removed by Filter: 0
11. 0.312 0.312 ↓ 0.0 0 24

Index Scan using idx1_nu_cumstpf on cumstpf c1 (cost=0.43..0.73 rows=1 width=75) (actual time=0.013..0.013 rows=0 loops=24)

  • Index Cond: (alcnbr = cnmocpp.mocnbr)
  • Filter: (((alstz5)::text = 'A'::text) OR (alsadt >= (('now'::cstring)::date - '2 years'::interval)))
  • Rows Removed by Filter: 1
12. 0.190 0.190 ↑ 1.0 1 10

Index Scan using idx0_u_customer_account_key on customer_account_key keys (cost=0.43..0.59 rows=1 width=12) (actual time=0.017..0.019 rows=1 loops=10)

  • Index Cond: ((src_value)::text = ((c1.alcnbr)::character varying)::text)
13. 1,932.820 1,932.820 ↑ 1.0 1 10

Index Scan using "cbizrep_CBIZREL0" on cbizrep (cost=0.43..27,561.03 rows=1 width=21) (actual time=110.366..193.282 rows=1 loops=10)

  • Index Cond: ((izcnbr = c1.alcnbr) AND (iznuo9 = '1'::numeric))
14. 0.110 0.110 ↑ 1.0 1 10

Index Scan using idx01_cml_tmpservicedisc on cml_tmpservicedisc sd (cost=0.42..0.49 rows=1 width=71) (actual time=0.009..0.011 rows=1 loops=10)

  • Index Cond: (source_billing_system_account_id_rgu = c1.alcnbr)
15. 2,954.357 5,114.910 ↓ 1.0 1,269,555 10

Materialize (cost=41,355.63..100,159.31 rows=1,269,526 width=29) (actual time=88.302..511.491 rows=1,269,555 loops=10)

16. 934.086 2,160.553 ↓ 1.0 1,269,555 1

Hash Left Join (cost=41,355.63..93,811.68 rows=1,269,526 width=29) (actual time=883.005..2,160.553 rows=1,269,555 loops=1)

  • Hash Cond: ((c.accountnumber)::text = (e.accountnumber)::text)
17. 344.573 344.573 ↓ 1.0 1,269,555 1

Seq Scan on customeraccounts c (cost=0.00..35,885.26 rows=1,269,526 width=8) (actual time=0.008..344.573 rows=1,269,555 loops=1)

18. 523.371 881.894 ↓ 1.0 1,181,020 1

Hash (cost=26,593.06..26,593.06 rows=1,181,006 width=36) (actual time=881.893..881.894 rows=1,181,020 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 96,513kB
19. 358.523 358.523 ↓ 1.0 1,181,020 1

Seq Scan on emailsubscriptionaccounts e (cost=0.00..26,593.06 rows=1,181,006 width=36) (actual time=0.009..358.523 rows=1,181,020 loops=1)

20. 0.070 0.070 ↓ 0.0 0 10

Index Scan using idx01_cml_adjustments on cml_adjustments adj (cost=0.15..0.17 rows=1 width=68) (actual time=0.007..0.007 rows=0 loops=10)

  • Index Cond: ((source_billing_system_account_id_adj = c1.alcnbr) AND (source_billing_system_housenumber_adj = cnmocpp.mohnum))
21.          

SubPlan (for Nested Loop Left Join)

22. 0.070 13,819.660 ↑ 1.0 1 10

Limit (cost=218,671.11..218,671.11 rows=1 width=5) (actual time=1,381.963..1,381.966 rows=1 loops=10)

23. 0.340 13,819.590 ↑ 12.0 1 10

Sort (cost=218,671.11..218,671.14 rows=12 width=5) (actual time=1,381.958..1,381.959 rows=1 loops=10)

  • Sort Key: cfcfcpp.cfacos DESC
  • Sort Method: quicksort Memory: 25kB
24. 13,819.250 13,819.250 ↑ 2.0 6 10

Seq Scan on cfcfcpp (cost=0.00..218,671.05 rows=12 width=5) (actual time=985.481..1,381.925 rows=6 loops=10)

  • Filter: (cfcnbr = c1.alcnbr)
  • Rows Removed by Filter: 7,567,799
Planning time : 6.951 ms
Execution time : 28,763.216 ms