explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AFAP : Vendor_Reports

Settings
# exclusive inclusive rows x rows loops node
1. 20.995 10,155.621 ↓ 405.2 81,050 1

Unique (cost=164,952,484,315.92..166,360,247,475.01 rows=200 width=158) (actual time=10,106.509..10,155.621 rows=81,050 loops=1)

2. 207.548 10,134.626 ↑ 2,701,055.6 104,238 1

Sort (cost=164,952,484,315.92..165,656,365,895.47 rows=281,552,631,819 width=158) (actual time=10,106.507..10,134.626 rows=104,238 loops=1)

  • Sort Key: cdrs_connections26.i_cdrs_connection, (length((dest.prefix)::text)) DESC
  • Sort Method: external sort Disk: 9848kB
3. 176.194 9,927.078 ↑ 2,701,055.6 104,238 1

Nested Loop (cost=9.54..739,875,233.83 rows=281,552,631,819 width=158) (actual time=278.917..9,927.078 rows=104,238 loops=1)

4. 1,437.093 6,415.268 ↑ 53.0 104,238 1

Nested Loop Left Join (cost=9.12..2,184,854.83 rows=5,522,056 width=88) (actual time=278.708..6,415.268 rows=104,238 loops=1)

5. 800.530 3,924.525 ↑ 6.8 81,050 1

Nested Loop Semi Join (cost=8.58..320,198.83 rows=552,368 width=80) (actual time=278.006..3,924.525 rows=81,050 loops=1)

  • Join Filter: (cdrs_connections26.i_connection = connections.i_connection)
  • Rows Removed by Join Filter: 11893214
6. 168.400 2,019.146 ↓ 1.0 1,104,849 1

Merge Append (cost=4.35..137,904.63 rows=1,104,736 width=80) (actual time=277.422..2,019.146 rows=1,104,849 loops=1)

  • Sort Key: cdrs_connections26.i_call
7. 365.467 365.467 ↓ 1.0 37,734 1

Index Scan using cdrs_connections26_i_call on cdrs_connections26 (cost=0.42..10,281.13 rows=37,507 width=80) (actual time=276.212..365.467 rows=37,734 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 92791
8. 168.199 168.199 ↓ 1.0 106,680 1

Index Scan using cdrs_connections1_i_call on cdrs_connections1 (cost=0.42..8,420.94 rows=106,669 width=80) (actual time=0.171..168.199 rows=106,680 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
9. 129.194 129.194 ↓ 1.0 90,206 1

Index Scan using cdrs_connections119_i_call on cdrs_connections119 (cost=0.29..7,070.28 rows=90,197 width=80) (actual time=0.089..129.194 rows=90,206 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
10. 137.961 137.961 ↓ 1.0 95,766 1

Index Scan using cdrs_connections12_i_call on cdrs_connections12 (cost=0.29..7,470.61 rows=95,756 width=80) (actual time=0.111..137.961 rows=95,766 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
11. 171.855 171.855 ↓ 1.0 129,308 1

Index Scan using cdrs_connections121_i_call on cdrs_connections121 (cost=0.42..10,099.05 rows=129,295 width=80) (actual time=0.112..171.855 rows=129,308 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
12. 113.154 113.154 ↓ 1.0 81,340 1

Index Scan using cdrs_connections122_i_call on cdrs_connections122 (cost=0.29..6,412.83 rows=81,332 width=80) (actual time=0.081..113.154 rows=81,340 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
13. 134.066 134.066 ↓ 1.0 98,839 1

Index Scan using cdrs_connections123_i_call on cdrs_connections123 (cost=0.29..7,786.01 rows=98,829 width=80) (actual time=0.109..134.066 rows=98,839 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
14. 184.615 184.615 ↓ 1.0 142,457 1

Index Scan using cdrs_connections124_i_call on cdrs_connections124 (cost=0.42..11,086.89 rows=142,443 width=80) (actual time=0.110..184.615 rows=142,457 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
15. 63.674 63.674 ↓ 1.0 48,167 1

Index Scan using cdrs_connections125_i_call on cdrs_connections125 (cost=0.29..3,761.86 rows=48,162 width=80) (actual time=0.110..63.674 rows=48,167 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
16. 123.893 123.893 ↓ 1.0 93,105 1

Index Scan using cdrs_connections116_i_call on cdrs_connections116 (cost=0.29..7,246.76 rows=93,096 width=80) (actual time=0.116..123.893 rows=93,105 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
17. 149.933 149.933 ↓ 1.0 114,696 1

Index Scan using cdrs_connections117_i_call on cdrs_connections117 (cost=0.42..8,959.64 rows=114,685 width=80) (actual time=0.104..149.933 rows=114,696 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
18. 108.735 108.735 ↑ 1.0 66,551 1

Index Scan using cdrs_connections126_i_call on cdrs_connections126 (cost=0.29..6,942.20 rows=66,765 width=80) (actual time=0.082..108.735 rows=66,551 loops=1)

  • Filter: ((duration > '0'::double precision) AND (call_setup_time >= '2019-09-24 00:00:00+00'::timestamp with time zone) AND (call_setup_time <= '2019-10-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 22081
19. 1,104.757 1,104.849 ↑ 1.0 11 1,104,849

Materialize (cost=4.23..12.78 rows=11 width=8) (actual time=0.000..0.001 rows=11 loops=1,104,849)

20. 0.045 0.092 ↑ 1.0 11 1

Bitmap Heap Scan on connections (cost=4.23..12.73 rows=11 width=8) (actual time=0.069..0.092 rows=11 loops=1)

  • Recheck Cond: (i_vendor = '113'::bigint)
  • Heap Blocks: exact=3
21. 0.047 0.047 ↑ 1.0 11 1

Bitmap Index Scan on connections_i_vendor (cost=0.00..4.23 rows=11 width=0) (actual time=0.047..0.047 rows=11 loops=1)

  • Index Cond: (i_vendor = '113'::bigint)
22. 1,053.650 1,053.650 ↑ 10.0 1 81,050

Index Only Scan using destinations_prefix2 on destinations dest (cost=0.54..3.28 rows=10 width=8) (actual time=0.012..0.013 rows=1 loops=81,050)

  • Index Cond: (prefix = ANY ((prefixes(cdrs_connections26.prefix))::text[]))
  • Heap Fetches: 104238
23. 312.714 3,335.616 ↑ 12.0 1 104,238

Append (cost=0.42..6.00 rows=12 width=8) (actual time=0.021..0.032 rows=1 loops=104,238)

24. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls26_pkey on calls26 (cost=0.42..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 5596
25. 312.714 312.714 ↓ 0.0 0 104,238

Index Only Scan using calls1_pkey on calls1 (cost=0.42..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 14494
26. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls119_pkey on calls119 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 5426
27. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls12_pkey on calls12 (cost=0.42..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 5598
28. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls121_pkey on calls121 (cost=0.42..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 8357
29. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls122_pkey on calls122 (cost=0.42..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 9578
30. 312.714 312.714 ↓ 0.0 0 104,238

Index Only Scan using calls123_pkey on calls123 (cost=0.43..0.52 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 9369
31. 312.714 312.714 ↓ 0.0 0 104,238

Index Only Scan using calls124_pkey on calls124 (cost=0.43..0.53 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 9092
32. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls125_pkey on calls125 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 3737
33. 312.714 312.714 ↓ 0.0 0 104,238

Index Only Scan using calls116_pkey on calls116 (cost=0.42..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 9487
34. 312.714 312.714 ↓ 0.0 0 104,238

Index Only Scan using calls117_pkey on calls117 (cost=0.42..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 16195
35. 208.476 208.476 ↓ 0.0 0 104,238

Index Only Scan using calls126_pkey on calls126 (cost=0.42..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=104,238)

  • Index Cond: (i_call = cdrs_connections26.i_call)
  • Heap Fetches: 7309
Planning time : 41.805 ms
Execution time : 10,171.800 ms