explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ORk1

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 35,659.939 ↑ 1.0 25 1

Limit (cost=138,948.45..161,690.67 rows=25 width=156) (actual time=35,588.992..35,659.939 rows=25 loops=1)

2. 0.100 35,659.931 ↑ 1,109.7 25 1

Merge Left Join (cost=138,948.45..25,375,535.22 rows=27,742 width=156) (actual time=35,588.991..35,659.931 rows=25 loops=1)

  • Merge Cond: (br.billingrunid = q2.billingrunid)
3. 0.046 33.398 ↑ 1,109.7 25 1

Merge Left Join (cost=2,627.84..5,113.93 rows=27,742 width=124) (actual time=21.258..33.398 rows=25 loops=1)

  • Merge Cond: (br.billingrunid = idr.billingrunid)
4. 12.113 12.113 ↑ 1,109.7 25 1

Index Scan Backward using billingrun_pkey on billingrun br (cost=0.29..2,007.53 rows=27,742 width=116) (actual time=0.013..12.113 rows=25 loops=1)

5. 6.995 21.239 ↑ 5,460.0 5 1

Sort (cost=2,627.55..2,695.80 rows=27,300 width=12) (actual time=21.238..21.239 rows=5 loops=1)

  • Sort Key: idr.billingrunid
  • Sort Method: quicksort Memory: 2048kB
6. 14.244 14.244 ↑ 1.0 27,300 1

Seq Scan on invdocrun idr (cost=0.00..616.00 rows=27,300 width=12) (actual time=0.177..14.244 rows=27,300 loops=1)

7. 4.402 35,558.283 ↑ 11.1 18 1

Sort (cost=136,320.62..136,321.12 rows=200 width=36) (actual time=35,558.272..35,558.283 rows=18 loops=1)

  • Sort Key: q2.billingrunid
  • Sort Method: quicksort Memory: 1151kB
8. 1.864 35,553.881 ↓ 74.8 14,957 1

Subquery Scan on q2 (cost=136,307.48..136,312.98 rows=200 width=36) (actual time=35,541.787..35,553.881 rows=14,957 loops=1)

9. 35.368 35,552.017 ↓ 74.8 14,957 1

HashAggregate (cost=136,307.48..136,310.98 rows=200 width=22) (actual time=35,541.786..35,552.017 rows=14,957 loops=1)

  • Group Key: bm.billingrunid
10. 762.822 35,516.649 ↑ 15.3 17,719 1

HashAggregate (cost=126,819.36..129,530.25 rows=271,089 width=14) (actual time=35,510.270..35,516.649 rows=17,719 loops=1)

  • Group Key: bm.billingrunid, pv.internalparametervaluecode
11. 965.096 34,753.827 ↓ 1.0 2,369,212 1

Hash Join (cost=79.34..109,055.88 rows=2,368,464 width=14) (actual time=1.883..34,753.827 rows=2,369,212 loops=1)

  • Hash Cond: ((bm.billingmessagetypeid)::text = (bmt.billingmessagetypeid)::text)
12. 33,787.011 33,787.011 ↓ 1.0 2,369,212 1

Seq Scan on billingmessage bm (cost=0.00..76,410.16 rows=2,368,464 width=27) (actual time=0.149..33,787.011 rows=2,369,212 loops=1)

  • Filter: ((billingphase)::text = 'billing'::text)
  • Rows Removed by Filter: 53521
13. 0.113 1.720 ↓ 1.0 321 1

Hash (cost=75.34..75.34 rows=320 width=53) (actual time=1.720..1.720 rows=321 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
14. 0.162 1.607 ↓ 1.0 321 1

Hash Join (cost=53.74..75.34 rows=320 width=53) (actual time=1.192..1.607 rows=321 loops=1)

  • Hash Cond: (bmt.billingmessageseverityparamid = pv.parametervalueid)
15. 0.291 0.291 ↓ 1.0 321 1

Seq Scan on billingmessagetype bmt (cost=0.00..17.20 rows=320 width=47) (actual time=0.027..0.291 rows=321 loops=1)

16. 0.142 1.154 ↑ 1.2 878 1

Hash (cost=40.55..40.55 rows=1,055 width=14) (actual time=1.154..1.154 rows=878 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
17. 1.012 1.012 ↑ 1.2 878 1

Seq Scan on parametervalue pv (cost=0.00..40.55 rows=1,055 width=14) (actual time=0.140..1.012 rows=878 loops=1)

18.          

SubPlan (for Merge Left Join)

19. 0.025 2.000 ↓ 0.0 0 25

Limit (cost=0.43..1.62 rows=1 width=4) (actual time=0.080..0.080 rows=0 loops=25)

20. 1.975 1.975 ↓ 0.0 0 25

Index Scan using invdoc_invdocrunid on invdoc id (cost=0.43..1,537.98 rows=1,288 width=4) (actual time=0.079..0.079 rows=0 loops=25)

  • Index Cond: (invdocrunid = idr.invdocrunid)
21. 0.075 66.150 ↑ 1.0 1 25

Aggregate (cost=907.96..907.97 rows=1 width=0) (actual time=2.646..2.646 rows=1 loops=25)

22. 66.075 66.075 ↑ 289.0 5 25

Index Only Scan using invoice_billingrunid_index on invoice (cost=0.43..904.35 rows=1,445 width=0) (actual time=1.059..2.643 rows=5 loops=25)

  • Index Cond: (billingrunid = br.billingrunid)
  • Heap Fetches: 134
Planning time : 3.091 ms
Execution time : 35,664.525 ms