explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hd6w

Settings
# exclusive inclusive rows x rows loops node
1. 175.222 2,016.630 ↓ 8.3 14,118 1

Sort (cost=8,444.55..8,448.82 rows=1,707 width=264) (actual time=1,965.566..2,016.630 rows=14,118 loops=1)

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

CTE temp

3. 119.833 1,378.113 ↓ 1.1 18,165 1

Unique (cost=7,132.12..7,558.97 rows=17,074 width=264) (actual time=1,133.059..1,378.113 rows=18,165 loops=1)

4. 0.000 1,258.280 ↓ 1.1 18,165 1

Sort (cost=7,132.12..7,174.81 rows=17,074 width=264) (actual time=1,133.053..1,258.280 rows=18,165 loops=1)

  • Sort Key: p._fld4820rref, nom._idrref, p._fld4826, serdatanom._idrref, ((serdatanom._description)::character varying), serdatanom._fld6741, pa
  • Sort Method: external merge Disk: 6,992kB
5. 163.866 858.611 ↓ 1.1 18,165 1

Append (cost=367.23..3,828.36 rows=17,074 width=264) (actual time=55.629..858.611 rows=18,165 loops=1)

6. 694.745 694.745 ↓ 1.1 18,157 1

Hash Join (cost=367.23..2,273.11 rows=17,066 width=161) (actual time=55.621..694.745 rows=18,157 loops=1)

7. 109.905 858.611 ↓ 1.1 18,165 1

Append (cost=367.23..3,828.36 rows=17,074 width=264) (actual time=55.629..858.611 rows=18,165 loops=1)

8. 231.462 694.745 ↓ 1.1 18,157 1

Hash Join (cost=367.23..2,273.11 rows=17,066 width=161) (actual time=55.621..694.745 rows=18,157 loops=1)

  • Hash Cond: (p._fld4824rref = partya._idrref)
9. 137.979 459.481 ↓ 1.1 18,157 1

Hash Join (cost=326.79..1,827.35 rows=17,066 width=132) (actual time=50.849..459.481 rows=18,157 loops=1)

  • Hash Cond: (p._fld4823rref = serdatanom._idrref)
10. 275.177 300.822 ↓ 1.1 18,157 1

Hash Join (cost=197.48..1,463.38 rows=17,066 width=89) (actual time=30.104..300.822 rows=18,157 loops=1)

  • Hash Cond: (p._fld4822rref = nom._idrref)
  • -> Seq Scan on _accumrgt4827 p (cost=0.00..1031.24 rows=17,066 width=89) (actual time=4.328..123.483 rows=18,157 loops
  • Filter: ((_fld4819rref = '\x9a3ba9ccd3d6777c11ea044efa603a86'::bytea) AND (_period > now()))
  • Rows Removed by Filter: 5,705
11. 25.645 25.645 ↓ 1.0 3,974 1

Hash (cost=147.84..147.84 rows=3,971 width=17) (actual time=25.642..25.645 rows=3,974 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 223kB
  • -> Index Only Scan using _reference89_s_hpk on _reference89 nom (cost=0.28..147.84 rows=3,971 width=17) (actual
  • Heap Fetches: 273
12. 20.680 20.680 ↑ 1.0 2,943 1

Hash (cost=92.47..92.47 rows=2,947 width=60) (actual time=20.678..20.680 rows=2,943 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 309kB
  • -> Seq Scan on _reference139 serdatanom (cost=0.00..92.47 rows=2,947 width=60) (actual time=0.076..9.933 rows=2,943 lo
13. 2.166 3.802 ↓ 1.0 573 1

Hash (cost=33.53..33.53 rows=553 width=295) (actual time=3.800..3.802 rows=573 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 189kB
14. 1.636 1.636 ↓ 1.0 573 1

Seq Scan on _reference107 partya (cost=0.00..33.53 rows=553 width=295) (actual time=0.030..1.636 rows=573 loops=1)

15. 0.221 53.961 ↑ 1.0 8 1

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

  • Hash Cond: (p_1._fld4752rref = partya_1._idrref)
16. 0.179 48.913 ↑ 1.0 8 1

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

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

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

  • Filter: ((_fld4755 > '0'::numeric) AND (_fld4748rref = '\x9a3ba9ccd3d6777c11ea044efa603a86'::bytea))
  • Rows Removed by Filter: 28,536
18. 22.791 22.791 ↑ 1.0 2,943 1

Hash (cost=92.47..92.47 rows=2,947 width=60) (actual time=22.789..22.791 rows=2,943 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 312kB
  • -> Seq Scan on _reference139 serdatanom_1 (cost=0.00..92.47 rows=2,947 width=60) (actual time=0.017..10.878 rows=2,943
19. 2.865 4.827 ↓ 1.0 573 1

Hash (cost=33.53..33.53 rows=553 width=295) (actual time=4.825..4.827 rows=573 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 187kB
20. 1.962 1.962 ↓ 1.0 573 1

Seq Scan on _reference107 partya_1 (cost=0.00..33.53 rows=553 width=295) (actual time=0.018..1.962 rows=573 loops=1)

21. 285.448 1,841.408 ↓ 8.3 14,118 1

HashAggregate (cost=768.33..793.94 rows=1,707 width=264) (actual time=1,756.872..1,841.408 rows=14,118 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,047
22. 1,555.960 1,555.960 ↓ 1.1 18,165 1

CTE Scan on temp (cost=0.00..341.48 rows=17,074 width=264) (actual time=1,133.068..1,555.960 rows=18,165 loops=1)

Planning time : 11.743 ms
Execution time : 2,070.158 ms