explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pZA : Vendor_Summary_Sept_part2

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 386,874.142 ↑ 1.0 50 1

Limit (cost=10,020,102,023.38..10,020,102,023.51 rows=50 width=178) (actual time=386,874.127..386,874.142 rows=50 loops=1)

2. 5.658 386,874.127 ↑ 109,844.2 50 1

Sort (cost=10,020,102,023.38..10,020,115,753.90 rows=5,492,208 width=178) (actual time=386,874.124..386,874.127 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,685.007 386,868.469 ↑ 840.8 6,532 1

GroupAggregate (cost=10,019,631,235.26..10,019,919,576.18 rows=5,492,208 width=178) (actual time=378,260.740..386,868.469 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. 15,240.826 384,183.462 ↑ 1.3 4,323,599 1

Sort (cost=10,019,631,235.26..10,019,644,965.78 rows=5,492,208 width=154) (actual time=378,078.712..384,183.462 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: 393152kB
5. 705.005 368,942.636 ↑ 1.3 4,323,599 1

Merge Join (cost=10,000,000,022.98..10,018,152,884.28 rows=5,492,208 width=154) (actual time=10,926.307..368,942.636 rows=4,323,599 loops=1)

  • Merge Cond: (vendors.i_vendor = connections.i_vendor)
6. 0.030 0.148 ↑ 46.9 11 1

Nested Loop (cost=22.83..74.75 rows=516 width=32) (actual time=0.087..0.148 rows=11 loops=1)

  • Join Filter: (x1.iso_4217 = vendors.base_currency)
  • Rows Removed by Join Filter: 10
7. 0.041 0.041 ↑ 14.9 11 1

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

8. 0.015 0.077 ↑ 2.0 2 11

Materialize (cost=22.69..30.33 rows=4 width=20) (actual time=0.006..0.007 rows=2 loops=11)

9. 0.002 0.062 ↑ 2.0 2 1

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

10. 0.008 0.038 ↑ 1.0 2 1

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

  • Recheck Cond: (i_customer = 1)
  • Heap Blocks: exact=1
11. 0.030 0.030 ↑ 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.030..0.030 rows=2 loops=1)

  • Index Cond: (i_customer = 1)
12. 0.003 0.022 ↑ 2.0 1 2

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

13. 0.007 0.019 ↑ 2.0 1 1

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

  • Recheck Cond: ((iso_4217 = 'USD'::bpchar) AND (i_customer = 1))
  • Heap Blocks: exact=1
14. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: ((iso_4217 = 'USD'::bpchar) AND (i_customer = 1))
15. 1,540.947 368,237.483 ↓ 2.5 4,323,599 1

Materialize (cost=10,000,000,000.15..10,018,135,610.95 rows=1,748,640 width=130) (actual time=10,926.170..368,237.483 rows=4,323,599 loops=1)

16. 164,951.408 366,696.536 ↓ 2.5 4,323,599 1

Nested Loop (cost=10,000,000,000.15..10,018,131,239.35 rows=1,748,640 width=130) (actual time=10,926.167..366,696.536 rows=4,323,599 loops=1)

  • Join Filter: (cdrs_connections.i_connection = connections.i_connection)
  • Rows Removed by Join Filter: 1288432502
17. 3.250 3.250 ↑ 1.0 299 1

Index Scan using connections_i_vendor on connections (cost=0.15..39.95 rows=299 width=16) (actual time=0.012..3.250 rows=299 loops=1)

18. 200,428.059 201,741.878 ↓ 2.5 4,323,599 299

Materialize (cost=10,000,000,000.00..10,000,115,028.60 rows=1,748,640 width=130) (actual time=0.006..674.722 rows=4,323,599 loops=299)

19. 1,313.819 1,313.819 ↓ 2.5 4,323,599 1

Seq Scan on t1 cdrs_connections (cost=10,000,000,000.00..10,000,072,131.40 rows=1,748,640 width=130) (actual time=0.064..1,313.819 rows=4,323,599 loops=1)

Planning time : 1.921 ms
Execution time : 386,976.017 ms