explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1A01 : Vendor_Summary_Sept_part3

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 19,929.049 ↑ 1.0 50 1

Limit (cost=4,451,698.02..4,451,698.15 rows=50 width=104) (actual time=19,929.027..19,929.049 rows=50 loops=1)

2. 5.698 19,929.034 ↑ 271,599.6 50 1

Sort (cost=4,451,698.02..4,485,647.97 rows=13,579,980 width=104) (actual time=19,929.024..19,929.034 rows=50 loops=1)

  • Sort Key: vendors.name, (((cdrs_connections.price_1 * x1.rate) / x2.rate)), (((cdrs_connections.price_n * x1.rate) / x2.rate)) DESC, (sum(cdrs_connections.duration)) DESC
  • Sort Method: top-N heapsort Memory: 32kB
3. 2,385.186 19,923.336 ↑ 2,079.0 6,532 1

GroupAggregate (cost=3,287,631.90..4,000,580.85 rows=13,579,980 width=104) (actual time=12,860.323..19,923.336 rows=6,532 loops=1)

  • Group Key: vendors.i_vendor, cdrs_connections.cdr_prefix, cdrs_connections.price_1, cdrs_connections.price_n, x1.rate, x2.rate
4. 14,069.170 17,538.150 ↑ 3.1 4,323,599 1

Sort (cost=3,287,631.90..3,321,581.85 rows=13,579,980 width=80) (actual time=12,668.655..17,538.150 rows=4,323,599 loops=1)

  • Sort Key: vendors.i_vendor, cdrs_connections.cdr_prefix, cdrs_connections.price_1, cdrs_connections.price_n, x1.rate, x2.rate
  • Sort Method: external merge Disk: 393160kB
5. 1,608.560 3,468.980 ↑ 3.1 4,323,599 1

Hash Join (cost=127.79..471,924.26 rows=13,579,980 width=80) (actual time=1.568..3,468.980 rows=4,323,599 loops=1)

  • Hash Cond: (cdrs_connections.i_connection = connections.i_connection)
6. 1,858.890 1,858.890 ↑ 1.0 4,323,599 1

Index Scan using t1_i_connection on t1 cdrs_connections (cost=0.43..308,845.85 rows=4,323,671 width=56) (actual time=0.016..1,858.890 rows=4,323,599 loops=1)

7. 0.186 1.530 ↑ 3.2 298 1

Hash (cost=115.60..115.60 rows=940 width=40) (actual time=1.530..1.530 rows=298 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
8. 0.259 1.344 ↑ 3.2 298 1

Hash Join (cost=67.15..115.60 rows=940 width=40) (actual time=0.498..1.344 rows=298 loops=1)

  • Hash Cond: (vendors.base_currency = x1.iso_4217)
9. 0.325 1.003 ↑ 1.0 298 1

Hash Join (cost=36.79..73.61 rows=299 width=28) (actual time=0.396..1.003 rows=298 loops=1)

  • Hash Cond: (connections.i_vendor = vendors.i_vendor)
10. 0.323 0.323 ↑ 1.0 299 1

Index Scan using connections_pkey on connections (cost=0.15..32.87 rows=299 width=16) (actual time=0.012..0.323 rows=299 loops=1)

11. 0.113 0.355 ↑ 1.0 164 1

Hash (cost=34.59..34.59 rows=164 width=20) (actual time=0.355..0.355 rows=164 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
12. 0.242 0.242 ↑ 1.0 164 1

Index Scan using vendors_pkey on vendors (cost=0.14..34.59 rows=164 width=20) (actual time=0.008..0.242 rows=164 loops=1)

13. 0.010 0.082 ↑ 2.0 2 1

Hash (cost=30.31..30.31 rows=4 width=20) (actual time=0.082..0.082 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.005 0.072 ↑ 2.0 2 1

Nested Loop (cost=22.69..30.31 rows=4 width=20) (actual time=0.068..0.072 rows=2 loops=1)

15. 0.010 0.041 ↑ 1.0 2 1

Bitmap Heap Scan on x_rates x1 (cost=18.40..22.19 rows=2 width=12) (actual time=0.040..0.041 rows=2 loops=1)

  • Recheck Cond: (i_customer = 1)
  • Heap Blocks: exact=1
16. 0.031 0.031 ↑ 1.0 2 1

Bitmap Index Scan on x_rates_iso_4217_i_customer (cost=0.00..18.40 rows=2 width=0) (actual time=0.031..0.031 rows=2 loops=1)

  • Index Cond: (i_customer = 1)
17. 0.006 0.026 ↑ 2.0 1 2

Materialize (cost=4.28..8.08 rows=2 width=8) (actual time=0.013..0.013 rows=1 loops=2)

18. 0.006 0.020 ↑ 2.0 1 1

Bitmap Heap Scan on x_rates x2 (cost=4.28..8.07 rows=2 width=8) (actual time=0.019..0.020 rows=1 loops=1)

  • Recheck Cond: ((iso_4217 = 'USD'::bpchar) AND (i_customer = 1))
  • Heap Blocks: exact=1
19. 0.014 0.014 ↑ 2.0 1 1

Bitmap Index Scan on x_rates_iso_4217_i_customer (cost=0.00..4.28 rows=2 width=0) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((iso_4217 = 'USD'::bpchar) AND (i_customer = 1))
Planning time : 1.985 ms
Execution time : 19,971.345 ms