explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wUa9

Settings
# exclusive inclusive rows x rows loops node
1. 838.244 2,681.177 ↑ 122.4 5,449 1

HashAggregate (cost=72,620.91..85,958.23 rows=666,866 width=70) (actual time=2,673.505..2,681.177 rows=5,449 loops=1)

  • Group Key: z.id, LEAST(((inh.value_num / (z.sqkm)::double precision) / '5000'::double precision), '1'::double precision), inh.pe_id, 3041, re.id
2. 900.985 1,842.933 ↑ 1.1 626,635 1

Hash Right Join (cost=2,204.15..47,613.44 rows=666,866 width=54) (actual time=28.922..1,842.933 rows=626,635 loops=1)

  • Hash Cond: (rtpezoav.zo_id = z.id)
3. 104.010 913.058 ↓ 1.1 626,635 1

Append (cost=0.00..29,873.07 rows=586,373 width=16) (actual time=0.011..913.058 rows=626,635 loops=1)

4. 16.907 16.907 ↑ 1.0 38,143 1

Seq Scan on rtpezoav (cost=0.00..900.82 rows=38,143 width=16) (actual time=0.011..16.907 rows=38,143 loops=1)

  • Filter: (ad_id = ANY ('{{1048,1051,1052,1071,NULL}}'::integer[]))
5. 145.942 792.141 ↓ 1.1 588,492 1

Subquery Scan on *SELECT* 2_1 (cost=2,707.54..26,040.38 rows=548,230 width=16) (actual time=53.725..792.141 rows=588,492 loops=1)

6. 352.065 646.199 ↓ 1.1 588,492 1

Hash Anti Join (cost=2,707.54..20,558.08 rows=548,230 width=56) (actual time=53.724..646.199 rows=588,492 loops=1)

  • Hash Cond: ((rtpe.id = av_1.pe_id) AND (av.zo_id = av_1.zo_id) AND (av.ad_id = av_1.ad_id))
7. 240.473 266.152 ↑ 1.0 588,492 1

Hash Join (cost=1,377.61..8,057.95 rows=588,840 width=20) (actual time=25.697..266.152 rows=588,492 loops=1)

  • Hash Cond: (rtpe.baseline_id = av.pe_id)
8. 0.062 0.062 ↑ 1.0 29 1

Seq Scan on rtpe (cost=0.00..1.29 rows=29 width=8) (actual time=0.018..0.062 rows=29 loops=1)

9. 12.632 25.617 ↑ 1.0 38,143 1

Hash (cost=900.82..900.82 rows=38,143 width=20) (actual time=25.617..25.617 rows=38,143 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2449kB
10. 12.985 12.985 ↑ 1.0 38,143 1

Seq Scan on rtpezoav av (cost=0.00..900.82 rows=38,143 width=20) (actual time=0.014..12.985 rows=38,143 loops=1)

  • Filter: (ad_id = ANY ('{{1048,1051,1052,1071,NULL}}'::integer[]))
11. 18.011 27.982 ↑ 1.0 38,143 1

Hash (cost=662.43..662.43 rows=38,143 width=12) (actual time=27.982..27.982 rows=38,143 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2151kB
12. 9.971 9.971 ↑ 1.0 38,143 1

Seq Scan on rtpezoav av_1 (cost=0.00..662.43 rows=38,143 width=12) (actual time=0.010..9.971 rows=38,143 loops=1)

13. 3.313 28.890 ↑ 1.1 5,449 1

Hash (cost=2,126.68..2,126.68 rows=6,197 width=38) (actual time=28.890..28.890 rows=5,449 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 490kB
14. 3.056 25.577 ↑ 1.1 5,449 1

Hash Join (cost=918.80..2,126.68 rows=6,197 width=38) (actual time=14.299..25.577 rows=5,449 loops=1)

  • Hash Cond: (z.re_id = re.id)
15. 4.556 22.488 ↑ 1.1 5,449 1

Hash Right Join (cost=917.47..2,104.49 rows=6,197 width=38) (actual time=14.259..22.488 rows=5,449 loops=1)

  • Hash Cond: ((rtsepezoav.pe_id = inh.pe_id) AND (rtsepezoav.zo_id = z.id))
16. 0.835 5.045 ↑ 1.0 5,449 1

Append (cost=0.00..1,084.17 rows=5,450 width=20) (actual time=1.360..5.045 rows=5,449 loops=1)

17. 4.175 4.175 ↑ 1.0 5,449 1

Seq Scan on rtsepezoav (cost=0.00..271.50 rows=5,449 width=20) (actual time=1.359..4.175 rows=5,449 loops=1)

  • Filter: ((ad_id = ANY ('{{21,NULL}}'::integer[])) AND (pe_id = 3) AND (se_id = 3041))
  • Rows Removed by Filter: 5437
18. 0.001 0.035 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=354.64..785.42 rows=1 width=20) (actual time=0.035..0.035 rows=0 loops=1)

19. 0.000 0.034 ↓ 0.0 0 1

Hash Anti Join (cost=354.64..785.41 rows=1 width=60) (actual time=0.034..0.034 rows=0 loops=1)

  • Hash Cond: ((rtpe_1.id = av_1_1.pe_id) AND (av_2.se_id = av_1_1.se_id) AND (av_2.zo_id = av_1_1.zo_id) AND (av_2.ad_id = av_1_1.ad_id))
20. 0.006 0.034 ↓ 0.0 0 1

Hash Join (cost=1.38..330.68 rows=5,073 width=24) (actual time=0.034..0.034 rows=0 loops=1)

  • Hash Cond: (av_2.pe_id = rtpe_1.baseline_id)
21. 0.014 0.014 ↑ 10,886.0 1 1

Seq Scan on rtsepezoav av_2 (cost=0.00..244.29 rows=10,886 width=24) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: ((ad_id = ANY ('{{21,NULL}}'::integer[])) AND (se_id = 3041))
22. 0.001 0.014 ↓ 0.0 0 1

Hash (cost=1.36..1.36 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on rtpe rtpe_1 (cost=0.00..1.36 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: (id = 3)
  • Rows Removed by Filter: 28
24. 0.000 0.000 ↓ 0.0 0

Hash (cost=244.29..244.29 rows=5,449 width=16) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtsepezoav av_1_1 (cost=0.00..244.29 rows=5,449 width=16) (never executed)

  • Filter: ((pe_id = 3) AND (se_id = 3041))
26. 2.778 12.887 ↑ 1.1 5,449 1

Hash (cost=824.53..824.53 rows=6,196 width=26) (actual time=12.887..12.887 rows=5,449 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 384kB
27. 3.294 10.109 ↑ 1.1 5,449 1

Hash Join (cost=364.71..824.53 rows=6,196 width=26) (actual time=4.315..10.109 rows=5,449 loops=1)

  • Hash Cond: (inh.zo_id = z.id)
28. 2.536 3.635 ↑ 1.1 5,449 1

Bitmap Heap Scan on rtpezoav inh (cost=207.11..650.65 rows=6,196 width=16) (actual time=1.124..3.635 rows=5,449 loops=1)

  • Recheck Cond: (ad_id = 1051)
  • Filter: (pe_id = 3)
  • Rows Removed by Filter: 5449
  • Heap Blocks: exact=84
29. 1.099 1.099 ↓ 1.0 10,898 1

Bitmap Index Scan on rtpezoav_ad_id_zo_id_idx (cost=0.00..205.56 rows=10,836 width=0) (actual time=1.099..1.099 rows=10,898 loops=1)

  • Index Cond: (ad_id = 1051)
30. 1.868 3.180 ↑ 1.0 5,449 1

Hash (cost=89.49..89.49 rows=5,449 width=14) (actual time=3.180..3.180 rows=5,449 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 320kB
31. 1.312 1.312 ↑ 1.0 5,449 1

Seq Scan on rtzo z (cost=0.00..89.49 rows=5,449 width=14) (actual time=0.011..1.312 rows=5,449 loops=1)

32. 0.009 0.033 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=4) (actual time=0.033..0.033 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.024 0.024 ↑ 1.0 15 1

Seq Scan on rtre re (cost=0.00..1.15 rows=15 width=4) (actual time=0.020..0.024 rows=15 loops=1)

Planning time : 3.201 ms
Execution time : 2,690.508 ms