explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Unki

Settings
# exclusive inclusive rows x rows loops node
1. 152.187 2,315.313 ↓ 10.1 33,115 1

GroupAggregate (cost=238,454.77..240,092.58 rows=3,273 width=136) (actual time=2,149.726..2,315.313 rows=33,115 loops=1)

  • Group Key: b.id, b.sqkm, b.inh_sqkm, b.pe_id, 1, re.id
2.          

CTE base

3. 16.579 69.467 ↓ 1.0 33,115 1

Hash Join (cost=985.08..14,183.59 rows=32,728 width=31) (actual time=15.478..69.467 rows=33,115 loops=1)

  • Hash Cond: (inh.zo_id = z.id)
4. 37.537 37.665 ↓ 1.0 33,115 1

Bitmap Heap Scan on rtpezoav inh (cost=21.00..12,806.30 rows=32,728 width=16) (actual time=0.148..37.665 rows=33,115 loops=1)

  • Recheck Cond: (pe_id = 3)
  • Rows Removed by Index Recheck: 25780
  • Filter: (ad_id = 3)
  • Rows Removed by Filter: 393713
  • Heap Blocks: lossy=3328
5. 0.128 0.128 ↑ 13.0 33,280 1

Bitmap Index Scan on rtpezoav_pe_id_idx (cost=0.00..12.81 rows=433,887 width=0) (actual time=0.128..0.128 rows=33,280 loops=1)

  • Index Cond: (pe_id = 3)
6. 8.368 15.223 ↑ 1.0 33,115 1

Hash (cost=550.15..550.15 rows=33,115 width=19) (actual time=15.223..15.223 rows=33,115 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2221kB
7. 6.855 6.855 ↑ 1.0 33,115 1

Seq Scan on rtzo z (cost=0.00..550.15 rows=33,115 width=19) (actual time=0.013..6.855 rows=33,115 loops=1)

8. 175.701 2,163.126 ↓ 21.9 521,331 1

Sort (cost=224,271.18..224,330.63 rows=23,779 width=80) (actual time=2,149.704..2,163.126 rows=521,331 loops=1)

  • Sort Key: b.id, b.sqkm, b.inh_sqkm, b.pe_id, re.id
  • Sort Method: quicksort Memory: 85601kB
9. 83.962 1,987.425 ↓ 21.9 521,331 1

Merge Left Join (cost=220,489.95..222,542.75 rows=23,779 width=80) (actual time=1,871.948..1,987.425 rows=521,331 loops=1)

  • Merge Cond: ((b.pe_id = rtsepezoav.pe_id) AND (b.id = rtsepezoav.zo_id))
10. 107.170 1,206.848 ↓ 66.2 264,547 1

Sort (cost=171,903.41..171,913.40 rows=3,996 width=64) (actual time=1,194.088..1,206.848 rows=264,547 loops=1)

  • Sort Key: b.pe_id, b.id
  • Sort Method: quicksort Memory: 36374kB
11. 250.958 1,099.678 ↓ 66.2 264,547 1

Hash Right Join (cost=745.78..171,664.36 rows=3,996 width=64) (actual time=86.454..1,099.678 rows=264,547 loops=1)

  • Hash Cond: ((rtpezoav.pe_id = b.pe_id) AND (rtpezoav.zo_id = b.id))
12. 105.406 762.287 ↓ 3.3 3,174,564 1

Append (cost=0.00..127,023.92 rows=974,549 width=20) (actual time=0.012..762.287 rows=3,174,564 loops=1)

13. 92.822 92.822 ↑ 1.0 529,094 1

Seq Scan on rtpezoav (cost=0.00..24,417.19 rows=531,572 width=20) (actual time=0.011..92.822 rows=529,094 loops=1)

  • Filter: (ad_id = ANY ('{{3,4,7,68,69,70,74,104,NULL}}'::integer[]))
  • Rows Removed by Filter: 324562
14. 125.362 564.059 ↓ 6.0 2,645,470 1

Subquery Scan on *SELECT* 2_1 (cost=17,059.75..97,733.99 rows=442,977 width=20) (actual time=101.874..564.059 rows=2,645,470 loops=1)

15. 40.010 438.697 ↓ 6.0 2,645,470 1

Gather (cost=17,059.75..93,304.22 rows=442,977 width=56) (actual time=101.872..438.697 rows=2,645,470 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 207.212 398.687 ↓ 4.8 881,823 3 / 3

Parallel Hash Anti Join (cost=16,059.75..48,006.52 rows=184,574 width=56) (actual time=94.928..398.687 rows=881,823 loops=3)

  • Hash Cond: ((rtpe.id = av_1.pe_id) AND (av.zo_id = av_1.zo_id) AND (av.ad_id = av_1.ad_id))
17. 64.011 98.777 ↓ 4.0 881,823 3 / 3

Hash Join (cost=1.27..16,882.14 rows=221,488 width=20) (actual time=0.046..98.777 rows=881,823 loops=3)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
18. 34.746 34.746 ↑ 1.3 176,365 3 / 3

Parallel Seq Scan on rtpezoav av (cost=0.00..13,835.41 rows=221,488 width=20) (actual time=0.013..34.746 rows=176,365 loops=3)

  • Filter: (ad_id = ANY ('{{3,4,7,68,69,70,74,104,NULL}}'::integer[]))
  • Rows Removed by Filter: 108187
19. 0.004 0.020 ↑ 1.2 10 3 / 3

Hash (cost=1.12..1.12 rows=12 width=8) (actual time=0.020..0.020 rows=10 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.016 0.016 ↑ 1.0 12 3 / 3

Seq Scan on rtpe (cost=0.00..1.12 rows=12 width=8) (actual time=0.015..0.016 rows=12 loops=3)

21. 60.703 92.698 ↑ 1.2 284,552 3 / 3

Parallel Hash (cost=9,833.90..9,833.90 rows=355,690 width=12) (actual time=92.698..92.698 rows=284,552 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 48288kB
22. 31.995 31.995 ↑ 1.2 284,552 3 / 3

Parallel Seq Scan on rtpezoav av_1 (cost=0.00..9,833.90 rows=355,690 width=12) (actual time=0.030..31.995 rows=284,552 loops=3)

23. 4.862 86.433 ↓ 201.9 33,115 1

Hash (cost=743.32..743.32 rows=164 width=52) (actual time=86.433..86.433 rows=33,115 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2636kB
24. 5.107 81.571 ↓ 201.9 33,115 1

Hash Join (cost=1.02..743.32 rows=164 width=52) (actual time=15.501..81.571 rows=33,115 loops=1)

  • Hash Cond: (b.re_id = re.id)
25. 76.454 76.454 ↓ 1.0 33,115 1

CTE Scan on base b (cost=0.00..654.56 rows=32,728 width=52) (actual time=15.482..76.454 rows=33,115 loops=1)

26. 0.002 0.010 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on rtre re (cost=0.00..1.01 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

28. 418.795 696.615 ↓ 2.2 518,737 1

Sort (cost=48,586.53..49,181.56 rows=238,009 width=20) (actual time=677.851..696.615 rows=518,737 loops=1)

  • Sort Key: rtsepezoav.pe_id, rtsepezoav.zo_id
  • Sort Method: quicksort Memory: 85453kB
29. 25.111 277.820 ↓ 3.3 779,217 1

Append (cost=0.00..27,331.55 rows=238,009 width=20) (actual time=0.013..277.820 rows=779,217 loops=1)

30. 21.690 21.690 ↓ 1.0 130,055 1

Seq Scan on rtsepezoav (cost=0.00..6,116.26 rows=129,823 width=20) (actual time=0.013..21.690 rows=130,055 loops=1)

  • Filter: ((se_id = 1) AND (ad_id = ANY ('{{31,95,NULL}}'::integer[])))
  • Rows Removed by Filter: 129038
31. 30.876 231.019 ↓ 6.0 649,162 1

Subquery Scan on *SELECT* 2 (cost=7,742.39..20,025.24 rows=108,186 width=20) (actual time=33.356..231.019 rows=649,162 loops=1)

32. 100.566 200.143 ↓ 6.0 649,162 1

Hash Anti Join (cost=7,742.39..18,943.38 rows=108,186 width=60) (actual time=33.355..200.143 rows=649,162 loops=1)

  • Hash Cond: ((av_2.se_id = av_1_1.se_id) AND (rtpe_1.id = av_1_1.pe_id) AND (av_2.zo_id = av_1_1.zo_id) AND (av_2.ad_id = av_1_1.ad_id))
33. 45.494 66.322 ↓ 5.0 649,162 1

Hash Join (cost=1.27..7,902.60 rows=129,823 width=24) (actual time=0.025..66.322 rows=649,162 loops=1)

  • Hash Cond: (av_2.pe_id = rtpe_1.baseline_id)
34. 20.819 20.819 ↓ 1.0 130,055 1

Seq Scan on rtsepezoav av_2 (cost=0.00..6,116.26 rows=129,823 width=24) (actual time=0.009..20.819 rows=130,055 loops=1)

  • Filter: ((se_id = 1) AND (ad_id = ANY ('{{31,95,NULL}}'::integer[])))
  • Rows Removed by Filter: 129038
35. 0.004 0.009 ↑ 1.2 10 1

Hash (cost=1.12..1.12 rows=12 width=8) (actual time=0.009..0.009 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.005 0.005 ↑ 1.0 12 1

Seq Scan on rtpe rtpe_1 (cost=0.00..1.12 rows=12 width=8) (actual time=0.004..0.005 rows=12 loops=1)

37. 14.248 33.255 ↓ 1.0 130,055 1

Hash (cost=5,144.66..5,144.66 rows=129,823 width=16) (actual time=33.255..33.255 rows=130,055 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7121kB
38. 19.007 19.007 ↓ 1.0 130,055 1

Seq Scan on rtsepezoav av_1_1 (cost=0.00..5,144.66 rows=129,823 width=16) (actual time=0.005..19.007 rows=130,055 loops=1)

  • Filter: (se_id = 1)
  • Rows Removed by Filter: 129038
Planning time : 3.070 ms
Execution time : 2,332.026 ms