explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zucG

Settings
# exclusive inclusive rows x rows loops node
1. 727.270 1,544.106 ↓ 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,544.086..1,544.106 rows=0 loops=1)

2. 10.215 816.836 ↓ 48,364.5 96,729 1

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

3. 50.956 806.621 ↓ 48,364.5 96,729 1

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

4. 22.266 755.665 ↓ 48,364.5 96,729 1

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

  • Sort Key: c.zoneid
  • Sort Method: quicksort Memory: 10,629kB
5. 11.338 733.399 ↓ 48,364.5 96,729 1

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

  • Hash Cond: (z.zo_id = c.zoneid)
6. 0.090 0.146 ↑ 1.7 453 1

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

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=11
7. 0.056 0.056 ↑ 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.055..0.056 rows=453 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
8. 12.979 721.915 ↓ 96,729.0 96,729 1

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

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5,937kB
9. 22.916 708.936 ↓ 96,729.0 96,729 1

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

  • Filter: ((worker_hash(c.zoneid) >= 1,342,177,267) AND (worker_hash(c.zoneid) <= 1,431,655,751))
10. 67.673 686.020 ↓ 5,689.9 96,729 1

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

11. 23.758 618.347 ↓ 5,689.9 96,729 1

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

  • Sort Key: f.ownerid, f.zoneid
  • Sort Method: quicksort Memory: 10,629kB
12. 11.524 594.589 ↓ 5,689.9 96,729 1

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

  • Filter: (f.ownerid = f.to_owner)
13. 101.126 583.065 ↓ 28.8 96,729 1

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

14. 13.350 481.939 ↓ 28.8 96,729 1

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

  • Filter: ((v.r <= '10'::double precision) OR (v.nd_attraction > '1e-05'::double precision))
  • Rows Removed by Filter: 254
15. 124.959 468.589 ↓ 16.0 96,983 1

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

16. 105.740 343.630 ↓ 16.0 96,983 1

Sort (cost=15,808.96..15,824.08 rows=6,050 width=200) (actual time=333.199..343.630 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
17. 82.267 237.890 ↓ 16.0 96,983 1

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

  • Hash Cond: ((z_1.re_id = rere.re_id) AND (n.re_id = rere.re2_id))
18. 23.587 155.597 ↓ 16.0 96,983 1

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

  • Hash Cond: ((z_1.zo_id = com.zo_id) AND (n.br_id = com.br_id))
19. 31.190 132.003 ↓ 16.0 96,983 1

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

  • Hash Cond: (x.zo_id = z_1.zo_id)
20. 48.551 100.489 ↓ 26.7 96,983 1

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

  • Hash Cond: (x.co_id = n.co_id)
21. 20.696 29.952 ↑ 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=10.056..29.952 rows=97,381 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (ad_id = 4))
  • Heap Blocks: exact=4,085
22. 9.256 9.256 ↑ 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=9.256..9.256 rows=97,381 loops=1)

  • Index Cond: ((pe_id = 391) AND (ad_id = 4))
23. 3.039 21.986 ↓ 18.5 4,917 1

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,074kB
24. 2.090 18.947 ↓ 18.5 4,917 1

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

  • Hash Cond: (co.br_id = ic.br_id)
25. 5.192 16.833 ↓ 39.7 4,924 1

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

  • Hash Cond: (co.lo_id = n.co_id)
26. 1.673 1.673 ↑ 1.0 13,267 1

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

27. 3.141 9.968 ↓ 39.7 4,924 1

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 979kB
28. 4.879 6.827 ↓ 39.7 4,924 1

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

  • Hash Cond: ((n.shop_concept = (sh.sh_id)::double precision) AND (n.ct_id = sh.ct_id))
29. 1.460 1.925 ↑ 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.515..1.925 rows=4,924 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=284
30. 0.465 0.465 ↑ 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.465..0.465 rows=4,924 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
31. 0.004 0.023 ↑ 1.0 3 1

Hash (cost=12.77..12.77 rows=3 width=24) (actual time=0.022..0.023 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.011 0.019 ↑ 1.0 3 1

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

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=2
33. 0.008 0.008 ↑ 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.008..0.008 rows=3 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
34. 0.005 0.024 ↑ 1.1 16 1

Hash (cost=62.80..62.80 rows=17 width=16) (actual time=0.023..0.024 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.009 0.019 ↑ 1.1 16 1

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

  • Recheck Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 307))
  • Heap Blocks: exact=1
36. 0.010 0.010 ↑ 1.1 16 1

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

  • Index Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 307))
37. 0.113 0.324 ↑ 1.7 453 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
38. 0.171 0.211 ↑ 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.044..0.211 rows=453 loops=1)

  • Recheck Cond: ((pe_id = 391) AND (se_id = 12))
  • Heap Blocks: exact=11
39. 0.040 0.040 ↑ 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.039..0.040 rows=453 loops=1)

  • Index Cond: ((pe_id = 391) AND (se_id = 12))
40. 0.000 0.007 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
41. 0.007 0.007 ↓ 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.006..0.007 rows=0 loops=1)

  • Index Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 308))
42. 0.007 0.026 ↑ 1.0 9 1

Hash (cost=34.42..34.42 rows=9 width=16) (actual time=0.025..0.026 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
43. 0.009 0.019 ↑ 1.0 9 1

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

  • Recheck Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 306))
  • Heap Blocks: exact=1
44. 0.010 0.010 ↑ 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.009..0.010 rows=9 loops=1)

  • Index Cond: ((se_id = 12) AND (pe_id = 391) AND (ad_id = 306))
Planning time : 20.373 ms
Execution time : 1,544.550 ms