explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oaOW

Settings
# exclusive inclusive rows x rows loops node
1. 886.056 148,284.360 ↓ 6,573.2 1,314,632 1

Unique (cost=101,513,760,271.33..102,174,327,438.13 rows=200 width=247) (actual time=145,971.631..148,284.360 rows=1,314,632 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. 11,299.354 147,398.304 ↑ 61,521.0 2,147,454 1

Sort (cost=101,513,760,271.33..101,844,043,854.73 rows=132,113,433,360 width=247) (actual time=145,971.629..147,398.304 rows=2,147,454 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: 400120kB
3. 5,084.661 136,098.950 ↑ 61,521.0 2,147,454 1

Nested Loop (cost=46.15..345,306,783.79 rows=132,113,433,360 width=247) (actual time=636.782..136,098.950 rows=2,147,454 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. 44,047.341 70,514.605 ↑ 1.5 2,160,703 1

Nested Loop Left Join (cost=45.72..1,207,439.02 rows=3,297,673 width=144) (actual time=0.823..70,514.605 rows=2,160,703 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. 1,174.218 6,648.214 ↓ 4.0 1,321,270 1

Hash Semi Join (cost=45.19..109,283.87 rows=330,262 width=138) (actual time=0.472..6,648.214 rows=1,321,270 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. 1,347.841 5,473.771 ↓ 2.0 1,321,270 1

Hash Semi Join (cost=22.80..103,853.45 rows=660,524 width=146) (actual time=0.231..5,473.771 rows=1,321,270 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. 322.140 4,125.848 ↓ 1.0 1,321,273 1

Append (cost=0.42..93,014.99 rows=1,321,047 width=138) (actual time=0.124..4,125.848 rows=1,321,273 loops=1)

8. 849.105 849.105 ↓ 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.122..849.105 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. 371.580 371.580 ↓ 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..371.580 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. 46.018 46.018 ↑ 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.050..46.018 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. 538.915 538.915 ↓ 1.0 170,041 1

Index Scan using cdrs132_i_call on public.cdrs132 (cost=0.42..15,312.40 rows=170,040 width=135) (actual time=0.064..538.915 rows=170,041 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. 383.668 383.668 ↓ 1.0 174,401 1

Index Scan using cdrs104_i_call on public.cdrs104 (cost=0.42..11,422.44 rows=174,400 width=137) (actual time=0.070..383.668 rows=174,401 loops=1)

  • Output: cdrs104.i_account, cdrs104.cost, cdrs104.connect_time, cdrs104.disconnect_time, cdrs104.duration, cdrs104.cli_in, cdrs104.cld_in, cdrs104.billed_duration, cdrs104.result, cdrs104.i_cdr, cdrs104.delay, cdrs104.price_1, cdrs104.price_n, cdrs104.prefix, cdrs104.remote_ip, cdrs104.i_call
  • Filter: (cdrs104.duration > '0'::double precision)
13. 357.930 357.930 ↓ 1.0 162,646 1

Index Scan using cdrs105_i_call on public.cdrs105 (cost=0.42..10,690.80 rows=162,645 width=140) (actual time=0.079..357.930 rows=162,646 loops=1)

  • Output: cdrs105.i_account, cdrs105.cost, cdrs105.connect_time, cdrs105.disconnect_time, cdrs105.duration, cdrs105.cli_in, cdrs105.cld_in, cdrs105.billed_duration, cdrs105.result, cdrs105.i_cdr, cdrs105.delay, cdrs105.price_1, cdrs105.price_n, cdrs105.prefix, cdrs105.remote_ip, cdrs105.i_call
  • Filter: (cdrs105.duration > '0'::double precision)
14. 891.911 891.911 ↓ 1.0 276,099 1

Index Scan using cdrs101_i_call on public.cdrs101 (cost=0.42..19,643.81 rows=276,078 width=141) (actual time=0.071..891.911 rows=276,099 loops=1)

  • Output: cdrs101.i_account, cdrs101.cost, cdrs101.connect_time, cdrs101.disconnect_time, cdrs101.duration, cdrs101.cli_in, cdrs101.cld_in, cdrs101.billed_duration, cdrs101.result, cdrs101.i_cdr, cdrs101.delay, cdrs101.price_1, cdrs101.price_n, cdrs101.prefix, cdrs101.remote_ip, cdrs101.i_call
  • Filter: (cdrs101.duration > '0'::double precision)
15. 364.581 364.581 ↓ 1.0 113,824 1

Index Scan using cdrs102_i_call on public.cdrs102 (cost=0.42..8,179.15 rows=113,625 width=136) (actual time=0.065..364.581 rows=113,824 loops=1)

  • Output: cdrs102.i_account, cdrs102.cost, cdrs102.connect_time, cdrs102.disconnect_time, cdrs102.duration, cdrs102.cli_in, cdrs102.cld_in, cdrs102.billed_duration, cdrs102.result, cdrs102.i_cdr, cdrs102.delay, cdrs102.price_1, cdrs102.price_n, cdrs102.prefix, cdrs102.remote_ip, cdrs102.i_call
  • Filter: (cdrs102.duration > '0'::double precision)
16. 0.023 0.082 ↑ 1.2 81 1

Hash (cost=21.15..21.15 rows=99 width=8) (actual time=0.082..0.082 rows=81 loops=1)

  • Output: accounts.i_account
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
17. 0.033 0.059 ↑ 1.2 81 1

Bitmap Heap Scan on public.accounts (cost=8.91..21.15 rows=99 width=8) (actual time=0.030..0.059 rows=81 loops=1)

  • Output: accounts.i_account
  • Recheck Cond: (accounts.i_customer = '1'::bigint)
  • Heap Blocks: exact=11
18. 0.026 0.026 ↑ 1.2 81 1

Bitmap Index Scan on accounts_i_customer (cost=0.00..8.88 rows=99 width=0) (actual time=0.026..0.026 rows=81 loops=1)

  • Index Cond: (accounts.i_customer = '1'::bigint)
19. 0.026 0.225 ↑ 1.2 81 1

Hash (cost=21.15..21.15 rows=99 width=8) (actual time=0.225..0.225 rows=81 loops=1)

  • Output: accounts_1.i_account
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
20. 0.162 0.199 ↑ 1.2 81 1

Bitmap Heap Scan on public.accounts accounts_1 (cost=8.91..21.15 rows=99 width=8) (actual time=0.059..0.199 rows=81 loops=1)

  • Output: accounts_1.i_account
  • Output: accounts_1.i_account
  • Recheck Cond: (accounts_1.i_customer = '1'::bigint)
  • Heap Blocks: exact=11
21. 0.037 0.037 ↑ 1.2 81 1

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

  • Index Cond: (accounts_1.i_customer = '1'::bigint)
22. 19,819.050 19,819.050 ↑ 5.0 2 1,321,270

Index Only Scan using destinations_prefix2 on public.destinations dest (cost=0.53..3.23 rows=10 width=6) (actual time=0.009..0.015 rows=2 loops=1,321,270)

  • Output: dest.prefix
  • Index Cond: (dest.prefix = ANY ((prefixes(cdrs12.prefix))::text[]))
  • Heap Fetches: 2160703
23. 4,321.406 60,499.684 ↑ 8.0 1 2,160,703

Append (cost=0.43..4.11 rows=8 width=39) (actual time=0.017..0.028 rows=1 loops=2,160,703)

24. 8,642.812 8,642.812 ↓ 0.0 0 2,160,703

Index Scan using calls12_pkey on public.calls12 (cost=0.43..0.54 rows=1 width=39) (actual time=0.004..0.004 rows=0 loops=2,160,703)

  • 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-04 11:49:08+00'::timestamp with time zone))
  • Rows Removed by Filter: 0
25. 6,482.109 6,482.109 ↓ 0.0 0 2,160,703

Index Scan using calls72_pkey on public.calls72 (cost=0.43..0.54 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=2,160,703)

  • 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-04 11:49:08+00'::timestamp with time zone))
26. 6,482.109 6,482.109 ↓ 0.0 0 2,160,703

Index Scan using calls7_pkey on public.calls7 (cost=0.42..0.45 rows=1 width=38) (actual time=0.003..0.003 rows=0 loops=2,160,703)

  • 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-04 11:49:08+00'::timestamp with time zone))
27. 6,482.109 6,482.109 ↓ 0.0 0 2,160,703

Index Scan using calls132_pkey on public.calls132 (cost=0.42..0.51 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=2,160,703)

  • 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-04 11:49:08+00'::timestamp with time zone))
28. 6,482.109 6,482.109 ↓ 0.0 0 2,160,703

Index Scan using calls104_pkey on public.calls104 (cost=0.42..0.51 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=2,160,703)

  • Output: calls104.setup_time, calls104.cli, calls104.cld, calls104.i_call
  • Index Cond: (calls104.i_call = cdrs12.i_call)
  • Filter: ((calls104.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls104.setup_time <= '2019-01-04 11:49:08+00'::timestamp with time zone))
29. 6,482.109 6,482.109 ↓ 0.0 0 2,160,703

Index Scan using calls105_pkey on public.calls105 (cost=0.42..0.55 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=2,160,703)

  • Output: calls105.setup_time, calls105.cli, calls105.cld, calls105.i_call
  • Index Cond: (calls105.i_call = cdrs12.i_call)
  • Filter: ((calls105.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls105.setup_time <= '2019-01-04 11:49:08+00'::timestamp with time zone))
30. 8,642.812 8,642.812 ↓ 0.0 0 2,160,703

Index Scan using calls101_pkey on public.calls101 (cost=0.43..0.53 rows=1 width=39) (actual time=0.003..0.004 rows=0 loops=2,160,703)

  • Output: calls101.setup_time, calls101.cli, calls101.cld, calls101.i_call
  • Index Cond: (calls101.i_call = cdrs12.i_call)
  • Filter: ((calls101.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls101.setup_time <= '2019-01-04 11:49:08+00'::timestamp with time zone))
31. 6,482.109 6,482.109 ↓ 0.0 0 2,160,703

Index Scan using calls102_pkey on public.calls102 (cost=0.42..0.49 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=2,160,703)

  • Output: calls102.setup_time, calls102.cli, calls102.cld, calls102.i_call
  • Index Cond: (calls102.i_call = cdrs12.i_call)
  • Filter: ((calls102.setup_time >= '2019-01-02 00:00:00+00'::timestamp with time zone) AND (calls102.setup_time <= '2019-01-04 11:49:08+00'::timestamp with time zone))