explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i8UF

Settings
# exclusive inclusive rows x rows loops node
1. 596.192 1,457.134 ↓ 0.0 0 1

Insert on rtsepezocoav_p_452_119064 citus_table_alias (cost=18,647.22..18,647.31 rows=2 width=68) (actual time=1,457.116..1,457.134 rows=0 loops=1)

  • Buffers: shared hit=977,565 read=657 dirtied=1,368 written=711
2. 11.701 860.942 ↓ 48,364.5 96,729 1

Subquery Scan on *SELECT* (cost=18,647.22..18,647.31 rows=2 width=68) (actual time=790.306..860.942 rows=96,729 loops=1)

  • Buffers: shared hit=4,877
3. 53.555 849.241 ↓ 48,364.5 96,729 1

WindowAgg (cost=18,647.22..18,647.29 rows=2 width=24) (actual time=790.304..849.241 rows=96,729 loops=1)

  • Buffers: shared hit=4,877
4. 22.361 795.686 ↓ 48,364.5 96,729 1

Sort (cost=18,647.22..18,647.23 rows=2 width=36) (actual time=790.199..795.686 rows=96,729 loops=1)

  • Sort Key: c.zoneid
  • Sort Method: quicksort Memory: 10,629kB
  • Buffers: shared hit=4,877
5. 11.505 773.325 ↓ 48,364.5 96,729 1

Hash Join (cost=18,574.04..18,647.21 rows=2 width=36) (actual time=761.752..773.325 rows=96,729 loops=1)

  • Hash Cond: (z.zo_id = c.zoneid)
  • Buffers: shared hit=4,874
6. 0.103 0.192 ↑ 1.7 453 1

Bitmap Heap Scan on calc_zones_115744 z (cost=24.02..94.34 rows=755 width=20) (actual time=0.107..0.192 rows=453 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=11
  • Buffers: shared hit=14
7. 0.089 0.089 ↑ 1.7 453 1

Bitmap Index Scan on idx_calc_zones_sepe_115744 (cost=0.00..23.83 rows=755 width=0) (actual time=0.089..0.089 rows=453 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
  • Buffers: shared hit=3
8. 14.849 761.628 ↓ 96,729.0 96,729 1

Hash (cost=18,550.01..18,550.01 rows=1 width=20) (actual time=761.612..761.628 rows=96,729 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5,937kB
  • Buffers: shared hit=4,860
9. 23.988 746.779 ↓ 96,729.0 96,729 1

Subquery Scan on c (cost=18,548.60..18,550.01 rows=1 width=20) (actual time=649.130..746.779 rows=96,729 loops=1)

  • Filter: ((worker_hash(c.zoneid) >= 1,342,177,267) AND (worker_hash(c.zoneid) <= 1,431,655,751))
  • Buffers: shared hit=4,860
10. 69.166 722.791 ↓ 5,689.9 96,729 1

WindowAgg (cost=18,548.60..18,549.67 rows=17 width=28) (actual time=649.123..722.791 rows=96,729 loops=1)

  • Buffers: shared hit=4,860
11. 24.730 653.625 ↓ 5,689.9 96,729 1

Sort (cost=18,548.60..18,548.65 rows=17 width=44) (actual time=649.110..653.625 rows=96,729 loops=1)

  • Sort Key: f.ownerid, f.zoneid
  • Sort Method: quicksort Memory: 10,629kB
  • Buffers: shared hit=4,860
12. 13.484 628.895 ↓ 5,689.9 96,729 1

Subquery Scan on f (cost=15,808.96..18,548.26 rows=17 width=44) (actual time=354.712..628.895 rows=96,729 loops=1)

  • Filter: (f.ownerid = f.to_owner)
  • Buffers: shared hit=4,860
13. 104.486 615.411 ↓ 28.8 96,729 1

WindowAgg (cost=15,808.96..18,506.24 rows=3,361 width=48) (actual time=354.711..615.411 rows=96,729 loops=1)

  • Buffers: shared hit=4,860
14. 17.208 510.925 ↓ 28.8 96,729 1

Subquery Scan on v (cost=15,808.96..18,304.58 rows=3,361 width=56) (actual time=354.693..510.925 rows=96,729 loops=1)

  • Filter: ((v.r <= '10'::double precision) OR (v.nd_attraction > '1e-05'::double precision))
  • Rows Removed by Filter: 254
  • Buffers: shared hit=4,860
15. 128.041 493.717 ↓ 16.0 96,983 1

WindowAgg (cost=15,808.96..18,213.83 rows=6,050 width=56) (actual time=354.691..493.717 rows=96,983 loops=1)

  • Buffers: shared hit=4,860
16. 111.964 365.676 ↓ 16.0 96,983 1

Sort (cost=15,808.96..15,824.08 rows=6,050 width=200) (actual time=354.654..365.676 rows=96,983 loops=1)

  • Sort Key: ic.br2_id, x.zo_id, ((((COALESCE(com.value_num, '1'::double precision) * rere.value_num) * n.csv) * CASE WHEN ((n.shop_concept = '2'::double precision) AND (n.ct_id = 1)) THEN (('0.01'::double precision * GREATEST((('1'::double precision + (n.pn_score_pt / '80'::double precision)) + (ln((LEAST(GREATEST(n.activity_map_fte, '4000'::double precision), '150000'::double precision) / '60000'::double precision)) / '7'::double precision)), '0.1'::double precision)) * ('1'::double precision / exp((x.value_num * sh.ddp)))) WHEN ((n.shop_concept = '1'::double precision) AND (n.ct_id = 1)) THEN (('0.01'::double precision * GREATEST((((((((((((('1'::double precision + ((n.total_openinghours - '35'::double precision) * '0.018'::double precision)) + ((n.atm - '2'::double precision) * '0.05'::double precision)) + ('0.15'::double precision * (n.last_significant_works - '2'::double precision))) - ((n.open_saturday - '1'::double precision) * '0.05'::double precision)) + (ln((LEAST(GREATEST(n.activity_map_fte, '25000'::double precision), '150000'::double precision) / '60000'::double precision)) / '7'::double precision)) + (CASE n.centrally_located_avg WHEN '1'::double precision THEN '-0.18'::numeric WHEN '3'::double precision THEN 0.06 ELSE 0.0 END)::double precision) + ((GREATEST(n.nps, '30'::double precision) - '60'::double precision) / '600'::double precision)) + (n.pn_score_nonfashion * '0.01'::double precision)) + (CASE WHEN (n.pn_score_services = '0'::double precision) THEN '-0.03'::numeric WHEN (n.pn_score_services > '1'::double precision) THEN 0.05 ELSE 0.0 END)::double precision) + (CASE WHEN (n.parking = '1'::double precision) THEN 0.03 ELSE '0'::numeric END)::double precision) + (CASE n.passage_foot WHEN '1'::double precision THEN '-0.05'::numeric WHEN '4'::double precision THEN 0.03 WHEN '5'::double precision THEN 0.06 ELSE 0.0 END)::double precision) + (CASE WHEN (n.nr_fte <= '2'::double precision) THEN '-0.12'::numeric ELSE 0.0 END)::double precision), '0.01'::double precision)) * ('1'::double precision / ((GREATEST(LEAST(z_1.rev_wealthy, '1.8'::double precision), '0.6'::double precision) ^ '1.5'::double precision) * exp(((x.value_num * sh.ddp) + '0.2'::double precision))))) ELSE NULL::double precision END)) DESC
  • Sort Method: quicksort Memory: 28,834kB
  • Buffers: shared hit=4,860
17. 77.200 253.712 ↓ 16.0 96,983 1

Hash Join (cost=5,298.70..15,428.94 rows=6,050 width=200) (actual time=39.860..253.712 rows=96,983 loops=1)

  • Hash Cond: ((z_1.re_id = rere.re_id) AND (n.re_id = rere.re2_id))
  • Buffers: shared hit=4,857
18. 23.730 176.456 ↓ 16.0 96,983 1

Hash Left Join (cost=5,264.15..14,218.33 rows=6,050 width=200) (actual time=39.714..176.456 rows=96,983 loops=1)

  • Hash Cond: ((z_1.zo_id = com.zo_id) AND (n.br_id = com.br_id))
  • Buffers: shared hit=4,854
19. 32.221 152.710 ↓ 16.0 96,983 1

Hash Join (cost=5,247.96..14,170.38 rows=6,050 width=200) (actual time=39.690..152.710 rows=96,983 loops=1)

  • Hash Cond: (x.zo_id = z_1.zo_id)
  • Buffers: shared hit=4,853
20. 45.867 120.007 ↓ 26.7 96,983 1

Hash Join (cost=5,144.18..13,969.71 rows=3,637 width=184) (actual time=39.183..120.007 rows=96,983 loops=1)

  • Hash Cond: (x.co_id = n.co_id)
  • Buffers: shared hit=4,839
21. 35.747 47.231 ↑ 1.1 97,381 1

Bitmap Heap Scan on rtpezocoav_102300 x (cost=3,060.40..11,451.56 rows=106,144 width=16) (actual time=12.252..47.231 rows=97,381 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (ad_id = 4))
  • Heap Blocks: exact=4,085
  • Buffers: shared hit=4,461
22. 11.484 11.484 ↑ 1.1 97,381 1

Bitmap Index Scan on rtpezocoav_unique3_102300 (cost=0.00..3,033.87 rows=106,144 width=0) (actual time=11.483..11.484 rows=97,381 loops=1)

  • Index Cond: ((pe_id = 391) AND (ad_id = 4))
  • Buffers: shared hit=376
23. 4.263 26.909 ↓ 18.5 4,917 1

Hash (cost=2,080.46..2,080.46 rows=266 width=176) (actual time=26.903..26.909 rows=4,917 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,074kB
  • Buffers: shared hit=378
24. 2.192 22.646 ↓ 18.5 4,917 1

Hash Join (cost=1,819.19..2,080.46 rows=266 width=176) (actual time=13.039..22.646 rows=4,917 loops=1)

  • Hash Cond: (co.br_id = ic.br_id)
  • Buffers: shared hit=378
25. 5.618 20.393 ↓ 39.7 4,924 1

Hash Join (cost=1,756.18..2,011.84 rows=124 width=164) (actual time=12.952..20.393 rows=4,924 loops=1)

  • Hash Cond: (co.lo_id = n.co_id)
  • Buffers: shared hit=374
26. 1.856 1.856 ↑ 1.0 13,267 1

Seq Scan on rtco_102097 co (cost=0.00..204.67 rows=13,267 width=8) (actual time=0.009..1.856 rows=13,267 loops=1)

  • Buffers: shared hit=72
27. 4.328 12.919 ↓ 39.7 4,924 1

Hash (cost=1,754.63..1,754.63 rows=124 width=156) (actual time=12.916..12.919 rows=4,924 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 979kB
  • Buffers: shared hit=302
28. 5.251 8.591 ↓ 39.7 4,924 1

Hash Join (cost=189.90..1,754.63 rows=124 width=156) (actual time=0.717..8.591 rows=4,924 loops=1)

  • Hash Cond: ((n.shop_concept = (sh.sh_id)::double precision) AND (n.ct_id = sh.ct_id))
  • Buffers: shared hit=302
29. 2.717 3.285 ↑ 1.7 4,924 1

Bitmap Heap Scan on calc_nodes_115757 n (cost=177.09..1,668.18 rows=8,273 width=136) (actual time=0.636..3.285 rows=4,924 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=284
  • Buffers: shared hit=298
30. 0.568 0.568 ↑ 1.7 4,924 1

Bitmap Index Scan on idx_calc_nodes_sepe_115757 (cost=0.00..175.02 rows=8,273 width=0) (actual time=0.568..0.568 rows=4,924 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
  • Buffers: shared hit=14
31. 0.007 0.055 ↑ 1.0 3 1

Hash (cost=12.77..12.77 rows=3 width=24) (actual time=0.054..0.055 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
32. 0.018 0.048 ↑ 1.0 3 1

Bitmap Heap Scan on calc_shopconcepts_115702 sh (cost=4.30..12.77 rows=3 width=24) (actual time=0.039..0.048 rows=3 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=2
  • Buffers: shared hit=4
33. 0.030 0.030 ↑ 1.0 3 1

Bitmap Index Scan on idx_calc_shopconcepts_sepebr_115702 (cost=0.00..4.30 rows=3 width=0) (actual time=0.030..0.030 rows=3 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
  • Buffers: shared hit=2
34. 0.012 0.061 ↑ 1.1 16 1

Hash (cost=62.80..62.80 rows=17 width=16) (actual time=0.059..0.061 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
35. 0.013 0.049 ↑ 1.1 16 1

Bitmap Heap Scan on rmvsepebrbrav_115759 ic (cost=4.63..62.80 rows=17 width=16) (actual time=0.044..0.049 rows=16 loops=1)

  • Recheck Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 307))
  • Heap Blocks: exact=1
  • Buffers: shared hit=4
36. 0.036 0.036 ↑ 1.1 16 1

Bitmap Index Scan on rmvsepebrbrav_unique_115759 (cost=0.00..4.63 rows=17 width=0) (actual time=0.036..0.036 rows=16 loops=1)

  • Index Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 307))
  • Buffers: shared hit=3
37. 0.148 0.482 ↑ 1.7 453 1

Hash (cost=94.34..94.34 rows=755 width=16) (actual time=0.481..0.482 rows=453 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=14
38. 0.286 0.334 ↑ 1.7 453 1

Bitmap Heap Scan on calc_zones_115744 z_1 (cost=24.02..94.34 rows=755 width=16) (actual time=0.053..0.334 rows=453 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=11
  • Buffers: shared hit=14
39. 0.048 0.048 ↑ 1.7 453 1

Bitmap Index Scan on idx_calc_zones_sepe_115744 (cost=0.00..23.83 rows=755 width=0) (actual time=0.048..0.048 rows=453 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
  • Buffers: shared hit=3
40. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=16.17..16.17 rows=1 width=16) (actual time=0.015..0.016 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
41. 0.015 0.015 ↓ 0.0 0 1

Index Scan using rtsepezobrav_unique_115703 on rtsepezobrav_115703 com (cost=0.15..16.17 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 308))
  • Buffers: shared hit=1
42. 0.011 0.056 ↑ 1.0 9 1

Hash (cost=34.42..34.42 rows=9 width=16) (actual time=0.055..0.056 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
43. 0.014 0.045 ↑ 1.0 9 1

Bitmap Heap Scan on rmvseperereav_119269 rere (cost=4.40..34.42 rows=9 width=16) (actual time=0.042..0.045 rows=9 loops=1)

  • Recheck Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 306))
  • Heap Blocks: exact=1
  • Buffers: shared hit=3
44. 0.031 0.031 ↑ 1.0 9 1

Bitmap Index Scan on rmvseperereav_se_id_pe_id_re_id_re2_id_ad_id_idx_119269 (cost=0.00..4.40 rows=9 width=0) (actual time=0.031..0.031 rows=9 loops=1)

  • Index Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 306))
  • Buffers: shared hit=2
Planning time : 30.395 ms
Execution time : 1,463.073 ms