explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S78y : test calcallocations

Settings
# exclusive inclusive rows x rows loops node
1. 100.598 26,083.017 ↓ 59.8 83,577 1

WindowAgg (cost=4,973.29..5,029.17 rows=1,397 width=12) (actual time=25,975.804..26,083.017 rows=83,577 loops=1)

2. 21.926 25,982.419 ↓ 59.8 83,577 1

Sort (cost=4,973.29..4,976.78 rows=1,397 width=45) (actual time=25,975.783..25,982.419 rows=83,577 loops=1)

  • Sort Key: c.zoneid
  • Sort Method: quicksort Memory: 13991kB
3. 10.049 25,960.493 ↓ 59.8 83,577 1

Hash Join (cost=3,652.49..4,900.31 rows=1,397 width=45) (actual time=25,948.334..25,960.493 rows=83,577 loops=1)

  • Hash Cond: (calc_zones.zo_id = c.zoneid)
4. 0.190 2.127 ↑ 1.3 4,063 1

Append (cost=0.00..1,214.80 rows=5,079 width=33) (actual time=0.010..2.127 rows=4,063 loops=1)

5. 1.916 1.916 ↑ 1.0 4,063 1

Seq Scan on calc_zones (cost=0.00..361.89 rows=4,063 width=33) (actual time=0.009..1.916 rows=4,063 loops=1)

  • Filter: ((se_id = 1) AND (pe_id = 3))
  • Rows Removed by Filter: 4063
6. 0.000 0.021 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=455.46..852.91 rows=1,016 width=33) (actual time=0.021..0.021 rows=0 loops=1)

7. 0.010 0.021 ↓ 0.0 0 1

Hash Join (cost=455.46..842.75 rows=1,016 width=753) (actual time=0.021..0.021 rows=0 loops=1)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on calc_zones av (cost=454.40..816.29 rows=4,063 width=37) (never executed)

  • Filter: ((NOT (hashed SubPlan 1)) AND (se_id = 1))
9.          

SubPlan (forSeq Scan)

10. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.09..434.08 rows=8,126 width=4) (never executed)

  • Hash Cond: (av_1.pe_id = rtpe_1.id)
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on calc_zones av_1 (cost=0.00..321.26 rows=8,126 width=8) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=4 width=4) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtpe rtpe_1 (cost=0.00..1.04 rows=4 width=4) (never executed)

  • Filter: (baseline_id IS NOT NULL)
14. 0.000 0.011 ↓ 0.0 0 1

Hash (cost=1.05..1.05 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on rtpe (cost=0.00..1.05 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Filter: (id = 3)
  • Rows Removed by Filter: 3
16. 12.625 25,948.317 ↓ 1,519.6 83,577 1

Hash (cost=3,651.81..3,651.81 rows=55 width=16) (actual time=25,948.317..25,948.317 rows=83,577 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4942kB
17. 8.008 25,935.692 ↓ 1,519.6 83,577 1

Subquery Scan on c (cost=2,050.16..3,651.81 rows=55 width=16) (actual time=4,196.030..25,935.692 rows=83,577 loops=1)

18. 89.110 25,927.684 ↓ 1,519.6 83,577 1

WindowAgg (cost=2,050.16..3,651.26 rows=55 width=20) (actual time=4,196.030..25,927.684 rows=83,577 loops=1)

19. 380.931 25,838.574 ↓ 1,519.6 83,577 1

Subquery Scan on f (cost=2,050.16..3,648.78 rows=55 width=36) (actual time=4,195.738..25,838.574 rows=83,577 loops=1)

  • Filter: (f.ownerid = f.to_owner)
  • Rows Removed by Filter: 5181774
20. 16,781.882 25,457.643 ↓ 477.6 5,265,351 1

WindowAgg (cost=2,050.16..3,510.97 rows=11,025 width=44) (actual time=4,194.903..25,457.643 rows=5,265,351 loops=1)

21. 697.123 8,675.761 ↓ 477.6 5,265,351 1

Subquery Scan on v (cost=2,050.16..2,794.34 rows=11,025 width=69) (actual time=4,194.878..8,675.761 rows=5,265,351 loops=1)

  • Filter: ((v.r <= '10'::numeric) OR (v.nd_attraction > '1.00000000000000008e-05'::double precision))
  • Rows Removed by Filter: 1638
22. 2,913.878 7,978.638 ↓ 265.4 5,266,989 1

WindowAgg (cost=2,050.16..2,496.67 rows=19,845 width=73) (actual time=4,194.875..7,978.638 rows=5,266,989 loops=1)

23. 4,449.108 5,064.760 ↓ 265.4 5,266,989 1

Sort (cost=2,050.16..2,099.77 rows=19,845 width=37) (actual time=4,194.862..5,064.760 rows=5,266,989 loops=1)

  • Sort Key: ic.br2_id, ca.zo_id, ca.attraction DESC
  • Sort Method: quicksort Memory: 608092kB
24. 585.024 615.652 ↓ 265.4 5,266,989 1

Hash Join (cost=267.62..633.57 rows=19,845 width=37) (actual time=26.948..615.652 rows=5,266,989 loops=1)

  • Hash Cond: (ca.br_id = ic.br_id)
25. 28.546 28.546 ↓ 83.6 83,603 1

Function Scan on getattractions ca (cost=0.25..10.25 rows=1,000 width=28) (actual time=24.856..28.546 rows=83,603 loops=1)

26. 0.405 2.082 ↑ 1.0 3,969 1

Hash (cost=217.76..217.76 rows=3,969 width=13) (actual time=2.082..2.082 rows=3,969 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 207kB
27. 1.677 1.677 ↑ 1.0 3,969 1

Seq Scan on rtsepebrbrav ic (cost=0.00..217.76 rows=3,969 width=13) (actual time=0.011..1.677 rows=3,969 loops=1)

  • Filter: ((value_num > '0'::numeric) AND (se_id = 1) AND (pe_id = 3) AND (ad_id = 66))
  • Rows Removed by Filter: 3969