explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oXmv

Settings
# exclusive inclusive rows x rows loops node
1. 184.535 1,933.904 ↓ 7.8 14,116 1

Sort (cost=8,827.35..8,831.89 rows=1,813 width=264) (actual time=1,881.595..1,933.904 rows=14,116 loops=1)

  • Sort Key: temp.expiration
  • Sort Method: external merge Disk: 5,400kB
2.          

CTE temp

3. 135.576 1,257.539 ↓ 1.0 18,165 1

Unique (cost=7,432.87..7,886.14 rows=18,131 width=264) (actual time=984.382..1,257.539 rows=18,165 loops=1)

4. 363.679 1,121.963 ↓ 1.0 18,165 1

Sort (cost=7,432.87..7,478.19 rows=18,131 width=264) (actual time=984.374..1,121.963 rows=18,165 loops=1)

  • Sort Key: p._fld4820rref, nom._idrref, p._fld4826, serdatanom._idrref, ((serdatanom._description)::character varying), serdatanom._fld6741, partya._idrref, ((partya._description)::character varyin
  • Sort Method: external merge Disk: 6,992kB
5. 99.293 758.284 ↓ 1.0 18,165 1

Append (cost=367.23..3,917.45 rows=18,131 width=264) (actual time=59.040..758.284 rows=18,165 loops=1)

6. 198.084 612.323 ↓ 1.0 18,157 1

Hash Join (cost=367.23..2,351.62 rows=18,123 width=161) (actual time=59.030..612.323 rows=18,157 loops=1)

  • Hash Cond: (p._fld4824rref = partya._idrref)
7. 120.220 409.837 ↓ 1.0 18,157 1

Hash Join (cost=326.79..1,880.76 rows=18,123 width=132) (actual time=54.574..409.837 rows=18,157 loops=1)

  • Hash Cond: (p._fld4823rref = serdatanom._idrref)
8. 135.711 271.085 ↓ 1.0 18,157 1

Hash Join (cost=197.48..1,502.26 rows=18,123 width=89) (actual time=36.004..271.085 rows=18,157 loops=1)

  • Hash Cond: (p._fld4822rref = nom._idrref)
9. 103.749 103.749 ↓ 1.0 18,157 1

Seq Scan on _accumrgt4827 p (cost=0.00..1,055.59 rows=18,123 width=89) (actual time=4.319..103.749 rows=18,157 loops=1)

  • Filter: ((_fld4819rref = '\x9a3ba9ccd3d6777c11ea044efa603a86'::bytea) AND (_period > now()))
  • Rows Removed by Filter: 5,705
10. 16.094 31.625 ↓ 1.0 3,975 1

Hash (cost=147.84..147.84 rows=3,971 width=17) (actual time=31.623..31.625 rows=3,975 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 223kB
11. 15.531 15.531 ↓ 1.0 3,975 1

Index Only Scan using _reference89_s_hpk on _reference89 nom (cost=0.28..147.84 rows=3,971 width=17) (actual time=0.037..15.531 rows=3,975 loops=1)

  • Heap Fetches: 281
12. 9.469 18.532 ↑ 1.0 2,945 1

Hash (cost=92.47..92.47 rows=2,947 width=60) (actual time=18.530..18.532 rows=2,945 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 309kB
13. 9.063 9.063 ↑ 1.0 2,945 1

Seq Scan on _reference139 serdatanom (cost=0.00..92.47 rows=2,947 width=60) (actual time=0.015..9.063 rows=2,945 loops=1)

14. 2.346 4.402 ↓ 1.0 574 1

Hash (cost=33.53..33.53 rows=553 width=295) (actual time=4.400..4.402 rows=574 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 189kB
15. 2.056 2.056 ↓ 1.0 574 1

Seq Scan on _reference107 partya (cost=0.00..33.53 rows=553 width=295) (actual time=0.031..2.056 rows=574 loops=1)

16. 0.193 46.668 ↑ 1.0 8 1

Hash Join (cost=169.75..1,384.51 rows=8 width=189) (actual time=30.319..46.668 rows=8 loops=1)

  • Hash Cond: (p_1._fld4752rref = partya_1._idrref)
17. 0.130 42.483 ↑ 1.0 8 1

Hash Join (cost=129.31..1,343.86 rows=8 width=131) (actual time=26.255..42.483 rows=8 loops=1)

  • Hash Cond: (p_1._fld4751rref = serdatanom_1._idrref)
18. 21.182 21.182 ↑ 1.0 8 1

Seq Scan on _accumrgt4756 p_1 (cost=0.00..1,214.44 rows=8 width=88) (actual time=5.032..21.182 rows=8 loops=1)

  • Filter: ((_fld4755 > '0'::numeric) AND (_fld4748rref = '\x9a3ba9ccd3d6777c11ea044efa603a86'::bytea))
  • Rows Removed by Filter: 28,537
19. 10.944 21.171 ↑ 1.0 2,945 1

Hash (cost=92.47..92.47 rows=2,947 width=60) (actual time=21.169..21.171 rows=2,945 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 312kB
20. 10.227 10.227 ↑ 1.0 2,945 1

Seq Scan on _reference139 serdatanom_1 (cost=0.00..92.47 rows=2,947 width=60) (actual time=0.017..10.227 rows=2,945 loops=1)

21. 2.147 3.992 ↓ 1.0 574 1

Hash (cost=33.53..33.53 rows=553 width=295) (actual time=3.991..3.992 rows=574 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 187kB
22. 1.845 1.845 ↓ 1.0 574 1

Seq Scan on _reference107 partya_1 (cost=0.00..33.53 rows=553 width=295) (actual time=0.021..1.845 rows=574 loops=1)

23. 299.985 1,749.369 ↓ 7.8 14,116 1

HashAggregate (cost=815.89..843.09 rows=1,813 width=264) (actual time=1,658.580..1,749.369 rows=14,116 loops=1)

  • Group Key: temp.expiration, temp.nom, temp.otd, temp.ifo, temp.seria, temp.serianame, temp.partia, temp.partianame
  • Filter: (sum(temp.quantity) > '0'::numeric)
  • Rows Removed by Filter: 4,049
24. 1,449.384 1,449.384 ↓ 1.0 18,165 1

CTE Scan on temp (cost=0.00..362.62 rows=18,131 width=264) (actual time=984.396..1,449.384 rows=18,165 loops=1)

Planning time : 7.009 ms
Execution time : 1,989.652 ms