explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RENL

Settings
# exclusive inclusive rows x rows loops node
1. 446.634 60,325.159 ↓ 3,625.0 725,008 1

Unique (cost=34,227,836,977.55..34,452,839,098.03 rows=200 width=245) (actual time=59,220.969..60,325.159 rows=725,008 loops=1)

  • Output: calls12.setup_time, calls12.cli, calls12.cld, cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, (CASE WHEN (dest.prefix IS NULL) THEN cdrs12.prefix ELSE dest.prefix END), (length((dest.prefix)::text))
2. 5,305.588 59,878.525 ↑ 41,347.2 1,088,354 1

Sort (cost=34,227,836,977.55..34,340,338,037.79 rows=45,000,424,097 width=245) (actual time=59,220.966..59,878.525 rows=1,088,354 loops=1)

  • Output: calls12.setup_time, calls12.cli, calls12.cld, cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, (CASE WHEN (dest.prefix IS NULL) THEN cdrs12.prefix ELSE dest.prefix END), (length((dest.prefix)::text))
  • Sort Key: cdrs12.i_cdr, (length((dest.prefix)::text)) DESC
  • Sort Method: external sort Disk: 201904kB
3. 1,634.682 54,572.937 ↑ 41,347.2 1,088,354 1

Nested Loop (cost=40.97..117,480,164.72 rows=45,000,424,097 width=245) (actual time=462.629..54,572.937 rows=1,088,354 loops=1)

  • Output: calls12.setup_time, calls12.cli, calls12.cld, cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, CASE WHEN (dest.prefix IS NULL) THEN cdrs12.prefix ELSE dest.prefix END, length((dest.prefix)::text)
4. 22,273.371 35,319.263 ↑ 1.7 1,101,187 1

Nested Loop Left Join (cost=40.54..664,734.58 rows=1,826,456 width=142) (actual time=0.800..35,319.263 rows=1,101,187 loops=1)

  • Output: cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, cdrs12.i_call, dest.prefix
5. 668.432 3,537.783 ↓ 4.0 731,393 1

Hash Join (cost=40.01..56,491.58 rows=182,920 width=136) (actual time=0.442..3,537.783 rows=731,393 loops=1)

  • Output: cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, cdrs12.i_call
  • Hash Cond: (cdrs12.i_account = accounts_1.i_account)
6. 651.336 2,869.162 ↓ 2.0 731,393 1

Hash Join (cost=20.22..53,618.24 rows=365,839 width=144) (actual time=0.236..2,869.162 rows=731,393 loops=1)

  • Output: cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, cdrs12.i_call, accounts.i_account
  • Hash Cond: (cdrs12.i_account = accounts.i_account)
7. 161.808 2,217.743 ↑ 1.0 731,393 1

Append (cost=0.42..47,891.35 rows=731,678 width=137) (actual time=0.126..2,217.743 rows=731,393 loops=1)

8. 779.816 779.816 ↓ 1.0 230,075 1

Index Scan using cdrs12_i_call on public.cdrs12 (cost=0.42..15,043.40 rows=230,073 width=136) (actual time=0.125..779.816 rows=230,075 loops=1)

  • Output: cdrs12.i_account, cdrs12.cost, cdrs12.connect_time, cdrs12.disconnect_time, cdrs12.duration, cdrs12.cli_in, cdrs12.cld_in, cdrs12.billed_duration, cdrs12.result, cdrs12.i_cdr, cdrs12.delay, cdrs12.price_1, cdrs12.price_n, cdrs12.prefix, cdrs12.remote_ip, cdrs12.i_call
  • Filter: (cdrs12.duration > '0'::double precision)
9. 351.916 351.916 ↓ 1.0 171,408 1

Index Scan using cdrs72_i_call on public.cdrs72 (cost=0.42..11,220.06 rows=171,407 width=138) (actual time=0.068..351.916 rows=171,408 loops=1)

  • Output: cdrs72.i_account, cdrs72.cost, cdrs72.connect_time, cdrs72.disconnect_time, cdrs72.duration, cdrs72.cli_in, cdrs72.cld_in, cdrs72.billed_duration, cdrs72.result, cdrs72.i_cdr, cdrs72.delay, cdrs72.price_1, cdrs72.price_n, cdrs72.prefix, cdrs72.remote_ip, cdrs72.i_call
  • Filter: (cdrs72.duration > '0'::double precision)
10. 44.952 44.952 ↑ 1.0 22,779 1

Index Scan using cdrs7_i_call on public.cdrs7 (cost=0.29..1,502.92 rows=22,779 width=138) (actual time=0.056..44.952 rows=22,779 loops=1)

  • Output: cdrs7.i_account, cdrs7.cost, cdrs7.connect_time, cdrs7.disconnect_time, cdrs7.duration, cdrs7.cli_in, cdrs7.cld_in, cdrs7.billed_duration, cdrs7.result, cdrs7.i_cdr, cdrs7.delay, cdrs7.price_1, cdrs7.price_n, cdrs7.prefix, cdrs7.remote_ip, cdrs7.i_call
  • Filter: (cdrs7.duration > '0'::double precision)
11. 879.251 879.251 ↑ 1.0 307,131 1

Index Scan using cdrs132_i_call on public.cdrs132 (cost=0.42..20,124.97 rows=307,419 width=136) (actual time=0.056..879.251 rows=307,131 loops=1)

  • Output: cdrs132.i_account, cdrs132.cost, cdrs132.connect_time, cdrs132.disconnect_time, cdrs132.duration, cdrs132.cli_in, cdrs132.cld_in, cdrs132.billed_duration, cdrs132.result, cdrs132.i_cdr, cdrs132.delay, cdrs132.price_1, cdrs132.price_n, cdrs132.prefix, cdrs132.remote_ip, cdrs132.i_call
  • Filter: (cdrs132.duration > '0'::double precision)
12. 0.022 0.083 ↑ 1.0 81 1

Hash (cost=18.78..18.78 rows=81 width=8) (actual time=0.083..0.083 rows=81 loops=1)

  • Output: accounts.i_account
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
13. 0.032 0.061 ↑ 1.0 81 1

Bitmap Heap Scan on public.accounts (cost=8.77..18.78 rows=81 width=8) (actual time=0.033..0.061 rows=81 loops=1)

  • Output: accounts.i_account
  • Recheck Cond: (accounts.i_customer = '1'::bigint)
  • Heap Blocks: exact=9
14. 0.029 0.029 ↑ 1.0 81 1

Bitmap Index Scan on accounts_i_customer (cost=0.00..8.75 rows=81 width=0) (actual time=0.029..0.029 rows=81 loops=1)

  • Index Cond: (accounts.i_customer = '1'::bigint)
15. 0.030 0.189 ↑ 1.0 81 1

Hash (cost=18.78..18.78 rows=81 width=8) (actual time=0.189..0.189 rows=81 loops=1)

  • Output: accounts_1.i_account
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
16. 0.122 0.159 ↑ 1.0 81 1

Bitmap Heap Scan on public.accounts accounts_1 (cost=8.77..18.78 rows=81 width=8) (actual time=0.057..0.159 rows=81 loops=1)

  • Output: accounts_1.i_account
  • Recheck Cond: (accounts_1.i_customer = '1'::bigint)
  • Heap Blocks: exact=9
17. 0.037 0.037 ↑ 1.0 81 1

Bitmap Index Scan on accounts_i_customer (cost=0.00..8.75 rows=81 width=0) (actual time=0.037..0.037 rows=81 loops=1)

  • Index Cond: (accounts_1.i_customer = '1'::bigint)
18. 9,508.109 9,508.109 ↑ 5.0 2 731,393

Index Only Scan using destinations_prefix2 on public.destinations dest (cost=0.53..3.23 rows=10 width=6) (actual time=0.008..0.013 rows=2 loops=731,393)

  • Output: dest.prefix
  • Index Cond: (dest.prefix = ANY ((prefixes(cdrs12.prefix))::text[]))
  • Heap Fetches: 1101187
19. 2,202.374 17,618.992 ↑ 4.0 1 1,101,187

Append (cost=0.43..2.32 rows=4 width=39) (actual time=0.011..0.016 rows=1 loops=1,101,187)

20. 4,404.748 4,404.748 ↓ 0.0 0 1,101,187

Index Scan using calls12_pkey on public.calls12 (cost=0.43..0.61 rows=1 width=39) (actual time=0.004..0.004 rows=0 loops=1,101,187)

  • Output: calls12.setup_time, calls12.cli, calls12.cld, calls12.i_call
  • Index Cond: (calls12.i_call = cdrs12.i_call)
  • Filter: ((calls12.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls12.setup_time <= '2019-01-03 15:18:28+00'::timestamp with time zone))
  • Rows Removed by Filter: 0
21. 3,303.561 3,303.561 ↓ 0.0 0 1,101,187

Index Scan using calls72_pkey on public.calls72 (cost=0.43..0.61 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=1,101,187)

  • Output: calls72.setup_time, calls72.cli, calls72.cld, calls72.i_call
  • Index Cond: (calls72.i_call = cdrs12.i_call)
  • Filter: ((calls72.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls72.setup_time <= '2019-01-03 15:18:28+00'::timestamp with time zone))
22. 3,303.561 3,303.561 ↓ 0.0 0 1,101,187

Index Scan using calls7_pkey on public.calls7 (cost=0.42..0.46 rows=1 width=38) (actual time=0.003..0.003 rows=0 loops=1,101,187)

  • Output: calls7.setup_time, calls7.cli, calls7.cld, calls7.i_call
  • Index Cond: (calls7.i_call = cdrs12.i_call)
  • Filter: ((calls7.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls7.setup_time <= '2019-01-03 15:18:28+00'::timestamp with time zone))
23. 4,404.748 4,404.748 ↓ 0.0 0 1,101,187

Index Scan using calls132_pkey on public.calls132 (cost=0.43..0.64 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=1,101,187)

  • Output: calls132.setup_time, calls132.cli, calls132.cld, calls132.i_call
  • Index Cond: (calls132.i_call = cdrs12.i_call)
  • Filter: ((calls132.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls132.setup_time <= '2019-01-03 15:18:28+00'::timestamp with time zone))
Planning time : 13.134 ms
Execution time : 61,905.841 ms