explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OW8

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,355.463 ↑ 1.0 25 1

Limit (cost=138,954.79..161,697.01 rows=25 width=156) (actual time=2,349.886..2,355.463 rows=25 loops=1)

2. 0.043 2,355.459 ↑ 1,109.7 25 1

Merge Left Join (cost=138,954.79..25,375,541.56 rows=27,742 width=156) (actual time=2,349.886..2,355.459 rows=25 loops=1)

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

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

  • Merge Cond: (br.billingrunid = idr.billingrunid)
4. 0.298 0.298 ↑ 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.011..0.298 rows=25 loops=1)

5. 6.256 19.450 ↑ 5,460.0 5 1

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

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

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

7. 7.875 2,330.024 ↑ 11.1 18 1

Sort (cost=136,326.96..136,327.46 rows=200 width=36) (actual time=2,330.022..2,330.024 rows=18 loops=1)

  • Sort Key: q2.billingrunid
  • Sort Method: quicksort Memory: 1151kB
8. 1.806 2,322.149 ↓ 74.8 14,957 1

Subquery Scan on q2 (cost=136,313.82..136,319.32 rows=200 width=36) (actual time=2,310.193..2,322.149 rows=14,957 loops=1)

9. 33.264 2,320.343 ↓ 74.8 14,957 1

HashAggregate (cost=136,313.82..136,317.32 rows=200 width=22) (actual time=2,310.193..2,320.343 rows=14,957 loops=1)

  • Group Key: bm.billingrunid
10. 575.229 2,287.079 ↑ 15.3 17,719 1

HashAggregate (cost=126,825.70..129,536.59 rows=271,089 width=14) (actual time=2,281.188..2,287.079 rows=17,719 loops=1)

  • Group Key: bm.billingrunid, pv.internalparametervaluecode
11. 732.861 1,711.850 ↓ 1.0 2,369,212 1

Hash Join (cost=79.34..109,060.82 rows=2,368,650 width=14) (actual time=1.683..1,711.850 rows=2,369,212 loops=1)

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

Seq Scan on billingmessage bm (cost=0.00..76,412.55 rows=2,368,650 width=27) (actual time=0.117..977.435 rows=2,369,212 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
14. 0.157 1.451 ↓ 1.0 321 1

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

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

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

16. 0.136 1.017 ↑ 1.2 878 1

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

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

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

18.          

SubPlan (for Merge Left Join)

19. 0.000 0.550 ↓ 0.0 0 25

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

20. 0.550 0.550 ↓ 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.022..0.022 rows=0 loops=25)

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

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

22. 5.000 5.000 ↑ 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=0.054..0.200 rows=5 loops=25)

  • Index Cond: (billingrunid = br.billingrunid)
  • Heap Fetches: 134
Planning time : 3.165 ms
Execution time : 2,360.800 ms