explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VBRu

Settings
# exclusive inclusive rows x rows loops node
1. 873.683 14,756.378 ↓ 0.0 0 1

Insert on rtsepezocoav_3608_4_19 (cost=279,966.57..280,072.36 rows=756 width=68) (actual time=14,756.378..14,756.378 rows=0 loops=1)

2. 12,637.631 13,882.695 ↓ 197.3 149,132 1

Subquery Scan on zzz (cost=279,966.57..280,072.36 rows=756 width=68) (actual time=1,027.978..13,882.695 rows=149,132 loops=1)

  • Filter: (zzz.allocation > 0)
  • Rows Removed by Filter: 50,430
3. 198.609 1,245.064 ↓ 88.0 199,562 1

WindowAgg (cost=279,966.57..280,040.24 rows=2,267 width=24) (actual time=1,027.908..1,245.064 rows=199,562 loops=1)

4. 47.483 1,046.455 ↓ 88.0 199,562 1

Sort (cost=279,966.57..279,972.23 rows=2,267 width=36) (actual time=1,027.890..1,046.455 rows=199,562 loops=1)

  • Sort Key: c.zoneid
  • Sort Method: quicksort Memory: 21,735kB
5. 35.454 998.972 ↓ 88.0 199,562 1

Hash Join (cost=260,898.55..279,840.22 rows=2,267 width=36) (actual time=957.866..998.972 rows=199,562 loops=1)

  • Hash Cond: (z.zo_id = c.zoneid)
6. 93.453 93.453 ↓ 1.0 54,883 1

Seq Scan on calc_zones z (cost=0.00..18,439.31 rows=54,821 width=20) (actual time=87.755..93.453 rows=54,883 loops=1)

  • Filter: ((se_id = 3,608) AND (pe_id = 4))
7. 28.731 870.065 ↓ 88.0 199,562 1

Hash (cost=260,870.21..260,870.21 rows=2,267 width=20) (actual time=870.065..870.065 rows=199,562 loops=1)

  • Buckets: 262,144 (originally 4096) Batches: 1 (originally 1) Memory Usage: 12,183kB
8. 11.546 841.334 ↓ 88.0 199,562 1

Subquery Scan on c (cost=260,705.86..260,870.21 rows=2,267 width=20) (actual time=665.810..841.334 rows=199,562 loops=1)

9. 156.447 829.788 ↓ 88.0 199,562 1

WindowAgg (cost=260,705.86..260,847.54 rows=2,267 width=28) (actual time=665.809..829.788 rows=199,562 loops=1)

10. 38.611 673.341 ↓ 88.0 199,562 1

Sort (cost=260,705.86..260,711.52 rows=2,267 width=44) (actual time=665.783..673.341 rows=199,562 loops=1)

  • Sort Key: f.ownerid, f.zoneid
  • Sort Method: quicksort Memory: 21,735kB
11. 14.838 634.730 ↓ 88.0 199,562 1

Subquery Scan on f (cost=226,579.91..260,579.51 rows=2,267 width=44) (actual time=444.475..634.730 rows=199,562 loops=1)

  • Filter: (f.ownerid = f.to_owner)
12. 167.470 619.892 ↑ 2.3 199,562 1

WindowAgg (cost=226,579.91..254,912.91 rows=453,328 width=48) (actual time=444.473..619.892 rows=199,562 loops=1)

13. 47.208 452.422 ↑ 2.3 199,562 1

Sort (cost=226,579.91..227,713.23 rows=453,328 width=56) (actual time=444.447..452.422 rows=199,562 loops=1)

  • Sort Key: v.to_owner, v.zoneid, v.attraction DESC
  • Sort Method: quicksort Memory: 34,208kB
14. 17.329 405.214 ↑ 2.3 199,562 1

Subquery Scan on v (cost=147,269.70..183,989.30 rows=453,328 width=56) (actual time=247.186..405.214 rows=199,562 loops=1)

  • Filter: ((v.r <= '10'::double precision) OR (v.nd_attraction > '1e-05'::double precision))
  • Rows Removed by Filter: 10,679
15. 118.526 387.885 ↑ 3.9 210,241 1

WindowAgg (cost=147,269.70..171,749.43 rows=815,991 width=64) (actual time=247.183..387.885 rows=210,241 loops=1)

16. 148.574 269.359 ↑ 3.9 210,241 1

Sort (cost=147,269.70..149,309.68 rows=815,991 width=56) (actual time=247.164..269.359 rows=210,241 loops=1)

  • Sort Key: rtsepebrbrav.br2_id, ca.zo_id, ca.attraction DESC
  • Sort Method: quicksort Memory: 35,710kB
17. 67.427 120.785 ↑ 3.9 210,241 1

Hash Join (cost=380.35..67,146.76 rows=815,991 width=56) (actual time=4.685..120.785 rows=210,241 loops=1)

  • Hash Cond: (ca.co_id = co.lo_id)
18. 48.790 48.790 ↑ 3.9 210,241 1

Index Scan using calc_attractions_3608_4_zo_idx on calc_attractions_3608_4 ca (cost=0.43..55,546.96 rows=815,991 width=40) (actual time=0.077..48.790 rows=210,241 loops=1)

  • Index Cond: (zo_id = ANY ('{639999,640000,640001,640002,640003,640004,640005,640006,640007,640008,640010,640011,640012,640013,640014,640015,640016,640017,640018,640019,640020,640021,640022,640023,640024
19. 1.550 4.568 ↑ 1.0 7,196 1

Hash (cost=289.97..289.97 rows=7,196 width=20) (actual time=4.568..4.568 rows=7,196 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 458kB
20. 2.024 3.018 ↑ 1.0 7,196 1

Hash Join (cost=74.06..289.97 rows=7,196 width=20) (actual time=0.278..3.018 rows=7,196 loops=1)

  • Hash Cond: (co.br_id = rtsepebrbrav.br_id)
21. 0.741 0.741 ↑ 1.0 7,196 1

Seq Scan on rtco co (cost=0.00..116.96 rows=7,196 width=8) (actual time=0.009..0.741 rows=7,196 loops=1)

22. 0.033 0.253 ↑ 1.0 153 1

Hash (cost=72.14..72.14 rows=154 width=16) (actual time=0.253..0.253 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
23. 0.010 0.220 ↑ 1.0 153 1

Append (cost=0.00..72.14 rows=154 width=16) (actual time=0.103..0.220 rows=153 loops=1)

24. 0.142 0.142 ↑ 1.0 153 1

Seq Scan on rtsepebrbrav (cost=0.00..18.03 rows=153 width=16) (actual time=0.102..0.142 rows=153 loops=1)

  • Filter: ((se_id = 3,608) AND (pe_id = 4) AND (ad_id = 16))
  • Rows Removed by Filter: 306
25. 0.000 0.068 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 2" (cost=29.64..53.34 rows=1 width=16) (actual time=0.068..0.068 rows=0 loops=1)

26. 0.000 0.068 ↓ 0.0 0 1

Hash Anti Join (cost=29.64..53.33 rows=1 width=64) (actual time=0.068..0.068 rows=0 loops=1)

  • Hash Cond: ((rtpe.id = av_1.pe_id) AND (av.se_id = av_1.se_id) AND (av.ad_id = av_1.ad_id) AND (av.br_id = av_1.br_id) AND (av.br2_id = av_1.br2_id))
27. 0.018 0.068 ↓ 0.0 0 1

Hash Join (cost=8.17..27.73 rows=132 width=28) (actual time=0.067..0.068 rows=0 loops=1)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
28. 0.020 0.020 ↑ 459.0 1 1

Seq Scan on rtsepebrbrav av (cost=0.00..16.88 rows=459 width=28) (actual time=0.020..0.020 rows=1 loops=1)

  • Filter: ((se_id = 3,608) AND (ad_id = 16))
29. 0.001 0.030 ↓ 0.0 0 1

Hash (cost=8.15..8.15 rows=1 width=8) (actual time=0.029..0.030 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
30. 0.029 0.029 ↑ 1.0 1 1

Index Only Scan using rtpe_id_baseline_id_idx on rtpe (cost=0.14..8.15 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)

  • Index Cond: (id = 4)
  • Heap Fetches: 1
31. 0.000 0.000 ↓ 0.0 0

Hash (cost=18.03..18.03 rows=153 width=20) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtsepebrbrav av_1 (cost=0.00..18.03 rows=153 width=20) (never executed)

  • Filter: ((pe_id = 4) AND (se_id = 3,608) AND (ad_id = 16))",,,,,,,,"explain_ExecutorEnd, auto_explain.c:415","par_sql"2020-06-23 14:14:08.740 CEST,"postgres","projectsBENELUX",29076,"116.202.128.187:63,777",5ef1f201.7194,2,"INSERT",2020-06-23 14:13:53 CEST,113/4070,16933136,LOG,00000,"duration: 14,779.459 ms statement: set search_path=leenbakker,public;