explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BOKD

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 44,232.187 ↓ 10.2 3,734,652 1

Append (cost=168,795.13..177,954.33 rows=366,368 width=56) (actual time=72.659..44,232.187 rows=3,734,652 loops=1)

  • Buffers: shared hit=164 read=16529
  • as select *
  • from calculateAttractions_ms3025_3(3)
  • PL/pgSQL function updateattractions(integer,integer,text) line 22 at EXECUTE
  • Query Text: explain analyze
  • select *
  • from calculateAttractions_ms3025_3(3)
2.          

CTE z

3. 37,868.333 40,460.238 ↓ 10.2 1,867,326 1

Hash Join (cost=5,600.83..168,795.13 rows=183,184 width=84) (actual time=72.651..40,460.238 rows=1,867,326 loops=1)

  • Output: 3025, 3, x.zo_id, x.co_id, sh.sh_id, ((((((COALESCE(rtsepezobrav.value_num, '1'::numeric) * rtseperereav.value_num) * n.competitive_strength))::double precision * CASE n.shop_concept WHEN '5'::numeric THEN (((('1'::numeric * CASE WHEN (n.comp_strength = '1'::numeric) THEN 0.7 ELSE CASE WHEN (n.comp_strength = '2'::numeric) THEN '1'::numeric ELSE CASE WHEN (n.comp_strength = '3'::numeric) THEN 1.3 ELSE '1'::numeric END END END) * '1'::numeric))::double precision / exp((((x.value_num * sh.ddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.599999999999999978'::double precision)))) WHEN '2'::numeric THEN (((((((((('1'::numeric * CASE WHEN (n.accessibility = '2'::numeric) THEN 1.18 ELSE CASE WHEN (n.accessibility = '0'::numeric) THEN 0.9 ELSE '1'::numeric END END) * CASE WHEN (n.visibility = '1'::numeric) THEN 0.8 ELSE CASE WHEN (n.visibility = '2'::numeric) THEN 0.9 ELSE CASE WHEN (n.visibility = '4'::numeric) THEN 1.03 ELSE CASE WHEN (n.visibility = '5'::numeric) THEN 1.05 ELSE '1'::numeric END END END END) * ('1'::numeric + ((n.passage - '3'::numeric) * 0.05))) * CASE WHEN (n.age < '5'::numeric) THEN 0.85 ELSE CASE WHEN (n.age > '15'::numeric) THEN 1.1 ELSE '1'::numeric END END) * CASE WHEN (n.cora = '1'::numeric) THEN CASE WHEN (n.re_id = 223341) THEN 1.6 ELSE 1.3 END ELSE '1'::numeric END) * CASE WHEN (n.concept = '1'::numeric) THEN 0.95 ELSE '1'::numeric END) * CASE WHEN (n.audio = '1'::numeric) THEN 1.05 ELSE '1'::numeric END) * CASE WHEN (n.re_id = 223343) THEN '1'::numeric ELSE CASE WHEN (n.re_id = 223342) THEN 0.7 ELSE CASE WHEN (n.re_id = 223341) THEN 0.88 ELSE '1'::numeric END END END) / exp(((((((x.value_num * sh.ddp) * GREATEST(exp(((- n.pn_other) / '40'::numeric)), 0.7)) * CASE WHEN (n.re_id = 223342) THEN 0.95 ELSE '1'::numeric END) * CASE WHEN (n.re_id = 223341) THEN 1.07 ELSE '1'::numeric END) * CASE WHEN (n.type = '1'::numeric) THEN 0.9 ELSE '1'::numeric END) * CASE WHEN (n.type = '3'::numeric) THEN 1.1 ELSE '1'::numeric END))))::double precision WHEN '4'::numeric THEN ('1'::double precision / exp((((x.value_num * sh.ddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.599999999999999978'::double precision)))) WHEN '3'::numeric THEN ('1'::double precision / exp((((x.value_num * sh.ddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.599999999999999978'::double precision)))) ELSE NULL::double precision END) * (n.fc)::double precision))::numeric, ((((((COALESCE(rtsepezobrav.value_num, '1'::numeric) * rtseperereav.value_num) * n.competitive_strength))::double precision * CASE n.shop_concept WHEN '5'::numeric THEN (((('1'::numeric * CASE WHEN (n.comp_strength = '1'::numeric) THEN 0.7 ELSE CASE WHEN (n.comp_strength = '2'::numeric) THEN '1'::numeric ELSE CASE WHEN (n.comp_strength = '3'::numeric) THEN 1.3 ELSE '1'::numeric END END END) * 0.001))::double precision / exp((((x.value_num * sh.nddp))::double precision + (((n.pn_other)::double precision ^ '1.19999999999999996'::double precision) * '0.00200000000000000004'::double precision)))) WHEN '2'::numeric THEN ((((('1'::numeric * ('1'::numeric + ((n.passage - '3'::numeric) * 0.05))) * CASE WHEN (n.cora = '1'::numeric) THEN 1.3 ELSE '1'::numeric END))::double precision * LEAST('0.0500000000000000028'::double precision, (((n.pn_other)::double precision ^ '1.89999999999999991'::double precision) * '0.00100000000000000002'::double precision))) / exp(((((x.value_num * sh.nddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.699999999999999956'::double precision)) * (CASE WHEN (n.re_id = 223342) THEN 0.8 ELSE '1'::numeric END)::double precision))) WHEN '4'::numeric THEN (('1'::double precision * LEAST('0.0500000000000000028'::double precision, (((n.pn_other)::double precision ^ '1.89999999999999991'::double precision) * '0.00100000000000000002'::double precision))) / exp((((x.value_num * sh.nddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.699999999999999956'::double precision)))) WHEN '3'::numeric THEN (('1'::double precision * LEAST('0.0500000000000000028'::double precision, (((n.pn_other)::double precision ^ '1.89999999999999991'::double precision) * '0.00100000000000000002'::double precision))) / exp((((x.value_num * sh.nddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.699999999999999956'::double precision)))) ELSE NULL::double precision END) * (n.fc)::double precision))::numeric
  • Hash Cond: ((x.co_id = n.co_id) AND (z_2.re_id = rtseperereav.re2_id))
  • Buffers: shared hit=164 read=16529
4. 1,730.492 2,547.687 ↑ 1.0 1,869,614 1

Hash Join (cost=1,550.46..71,028.13 rows=1,870,905 width=32) (actual time=28.206..2,547.687 rows=1,869,614 loops=1)

  • Output: x.zo_id, x.co_id, x.value_num, x.pe_id, z_2.se_id, z_2.re_id
  • Hash Cond: (x.zo_id = z_2.zo_id)
  • Buffers: shared hit=8 read=16354
5. 791.392 791.392 ↑ 1.0 1,869,614 1

Seq Scan on hansandersplanner.rtpezocoav x (cost=0.00..43,752.72 rows=1,870,905 width=24) (actual time=2.188..791.392 rows=1,869,614 loops=1)

  • Output: x.id, x.ad_id, x.pe_id, x.zo_id, x.co_id, x.value_num, x.value_text
  • Filter: ((x.pe_id = 3) AND (x.ad_id = 6))
  • Rows Removed by Filter: 11701
  • Buffers: shared hit=3 read=15530
6. 9.424 25.803 ↑ 1.3 19,782 1

Hash (cost=1,221.16..1,221.16 rows=26,344 width=12) (actual time=25.803..25.803 rows=19,782 loops=1)

  • Output: z_2.zo_id, z_2.se_id, z_2.re_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1107kB
  • Buffers: shared hit=2 read=824
7. 16.379 16.379 ↑ 1.3 19,782 1

Seq Scan on hansandersplanner.calc_zones z_2 (cost=0.00..1,221.16 rows=26,344 width=12) (actual time=0.020..16.379 rows=19,782 loops=1)

  • Output: z_2.zo_id, z_2.se_id, z_2.re_id
  • Filter: ((z_2.pe_id = 3) AND (z_2.se_id = 3025))
  • Buffers: shared hit=2 read=824
8. 6.224 44.218 ↓ 3.0 5,649 1

Hash (cost=4,022.38..4,022.38 rows=1,866 width=104) (actual time=44.218..44.218 rows=5,649 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, sh.sh_id, sh.ddp, sh.nddp, rtsepezobrav.value_num, rtseperereav.value_num, rtseperereav.re2_id
  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 681kB
  • Buffers: shared hit=156 read=175
9. 3.098 37.994 ↓ 3.0 5,649 1

Hash Join (cost=103.98..4,022.38 rows=1,866 width=104) (actual time=4.051..37.994 rows=5,649 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, sh.sh_id, sh.ddp, sh.nddp, rtsepezobrav.value_num, rtseperereav.value_num, rtseperereav.re2_id
  • Hash Cond: (n.re_id = rtseperereav.re_id)
  • Buffers: shared hit=156 read=175
10. 2.109 34.834 ↓ 1.0 1,883 1

Hash Join (cost=98.42..3,991.15 rows=1,866 width=95) (actual time=3.967..34.834 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, sh.sh_id, sh.ddp, sh.nddp, rtsepezobrav.value_num
  • Hash Cond: (n.shop_concept = (sh.sh_id)::numeric)
  • Buffers: shared hit=154 read=174
11. 8.341 32.684 ↑ 1.0 1,883 1

Hash Right Join (cost=97.25..3,961.91 rows=1,883 width=81) (actual time=3.853..32.684 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, rtsepezobrav.value_num
  • Hash Cond: ((rtsepezobrav.se_id = n.se_id) AND (rtsepezobrav.pe_id = n.pe_id) AND (rtsepezobrav.zo_id = n.zo_id) AND (rtsepezobrav.br_id = n.br_id))
  • Buffers: shared hit=151 read=173
12. 2.944 20.571 ↑ 1.0 19,783 1

Append (cost=0.00..2,577.01 rows=19,784 width=21) (actual time=0.021..20.571 rows=19,783 loops=1)

  • Buffers: shared hit=149 read=144
13. 9.272 9.272 ↑ 1.0 19,783 1

Seq Scan on hansandersplanner.rtsepezobrav (cost=0.00..492.20 rows=19,783 width=21) (actual time=0.020..9.272 rows=19,783 loops=1)

  • Output: rtsepezobrav.value_num, rtsepezobrav.se_id, rtsepezobrav.pe_id, rtsepezobrav.zo_id, rtsepezobrav.br_id
  • Filter: ((rtsepezobrav.se_id = 3025) AND (rtsepezobrav.pe_id = 3) AND (rtsepezobrav.ad_id = 75))
  • Buffers: shared hit=2 read=144
14. 0.001 8.355 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=937.32..1,985.89 rows=1 width=21) (actual time=8.355..8.355 rows=0 loops=1)

  • Output: "*SELECT* 2".value_num, "*SELECT* 2".se_id, "*SELECT* 2".pe_id, "*SELECT* 2".zo_id, "*SELECT* 2".br_id
  • Buffers: shared hit=147
15. 0.002 8.354 ↓ 0.0 0 1

Hash Anti Join (cost=937.32..1,985.88 rows=1 width=61) (actual time=8.354..8.354 rows=0 loops=1)

  • Output: NULL::integer, av.se_id, rtpe.id, av.zo_id, av.br_id, NULL::integer, av.value_num, NULL::text
  • 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.zo_id = av_1.zo_id) AND (av.br_id = av_1.br_id))
  • Buffers: shared hit=147
16. 2.614 8.352 ↓ 0.0 0 1

Nested Loop (cost=0.00..691.37 rows=19,050 width=25) (actual time=8.352..8.352 rows=0 loops=1)

  • Output: av.se_id, av.zo_id, av.br_id, av.value_num, av.ad_id, rtpe.id
  • Join Filter: (av.pe_id = rtpe.baseline_id)
  • Rows Removed by Join Filter: 19783
  • Buffers: shared hit=147
17. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on hansandersplanner.rtpe (cost=0.00..1.34 rows=1 width=8) (actual time=0.011..0.018 rows=1 loops=1)

  • Output: rtpe.id, rtpe.name, rtpe.is_default, rtpe.baseline_id, rtpe.sortkey, rtpe.user_id
  • Filter: (rtpe.id = 3)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
18. 5.720 5.720 ↑ 1.0 19,783 1

Seq Scan on hansandersplanner.rtsepezobrav av (cost=0.00..442.75 rows=19,783 width=25) (actual time=0.007..5.720 rows=19,783 loops=1)

  • Output: av.id, av.se_id, av.pe_id, av.zo_id, av.br_id, av.ad_id, av.value_num, av.value_text
  • Filter: ((av.se_id = 3025) AND (av.ad_id = 75))
  • Buffers: shared hit=146
19. 0.000 0.000 ↓ 0.0 0

Hash (cost=492.20..492.20 rows=19,783 width=20) (never executed)

  • Output: av_1.pe_id, av_1.se_id, av_1.zo_id, av_1.br_id, av_1.ad_id
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on hansandersplanner.rtsepezobrav av_1 (cost=0.00..492.20 rows=19,783 width=20) (never executed)

  • Output: av_1.pe_id, av_1.se_id, av_1.zo_id, av_1.br_id, av_1.ad_id
  • Filter: ((av_1.pe_id = 3) AND (av_1.se_id = 3025) AND (av_1.ad_id = 75))
21. 2.020 3.772 ↑ 1.0 1,883 1

Hash (cost=59.59..59.59 rows=1,883 width=84) (actual time=3.772..3.772 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, n.zo_id, n.br_id
  • Buckets: 2048 Batches: 1 Memory Usage: 192kB
  • Buffers: shared hit=2 read=29
22. 1.752 1.752 ↑ 1.0 1,883 1

Seq Scan on hansandersplanner.calc_nodes n (cost=0.00..59.59 rows=1,883 width=84) (actual time=0.035..1.752 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, n.zo_id, n.br_id
  • Filter: ((n.pe_id = 3) AND (n.se_id = 3025))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=2 read=29
23. 0.016 0.041 ↑ 1.0 6 1

Hash (cost=1.09..1.09 rows=6 width=18) (actual time=0.041..0.041 rows=6 loops=1)

  • Output: sh.sh_id, sh.ddp, sh.nddp, sh.se_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
24. 0.025 0.025 ↑ 1.0 6 1

Seq Scan on hansandersplanner.calc_shopconcepts sh (cost=0.00..1.09 rows=6 width=18) (actual time=0.021..0.025 rows=6 loops=1)

  • Output: sh.sh_id, sh.ddp, sh.nddp, sh.se_id
  • Filter: ((sh.pe_id = 3) AND (sh.se_id = 3025))
  • Buffers: shared read=1
25. 0.009 0.062 ↑ 1.1 9 1

Hash (cost=5.44..5.44 rows=10 width=21) (actual time=0.062..0.062 rows=9 loops=1)

  • Output: rtseperereav.value_num, rtseperereav.se_id, rtseperereav.pe_id, rtseperereav.re_id, rtseperereav.re2_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2 read=1
26. 0.003 0.053 ↑ 1.1 9 1

Append (cost=0.00..5.44 rows=10 width=21) (actual time=0.023..0.053 rows=9 loops=1)

  • Buffers: shared hit=2 read=1
27. 0.026 0.026 ↑ 1.0 9 1

Seq Scan on hansandersplanner.rtseperereav (cost=0.00..1.16 rows=9 width=21) (actual time=0.022..0.026 rows=9 loops=1)

  • Output: rtseperereav.value_num, rtseperereav.se_id, rtseperereav.pe_id, rtseperereav.re_id, rtseperereav.re2_id
  • Filter: ((rtseperereav.pe_id = 3) AND (rtseperereav.se_id = 3025) AND (rtseperereav.ad_id = 76))
  • Buffers: shared read=1
28. 0.001 0.024 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=1.36..4.24 rows=1 width=21) (actual time=0.024..0.024 rows=0 loops=1)

  • Output: "*SELECT* 2_1".value_num, "*SELECT* 2_1".se_id, "*SELECT* 2_1".pe_id, "*SELECT* 2_1".re_id, "*SELECT* 2_1".re2_id
  • Buffers: shared hit=2
29. 0.001 0.023 ↓ 0.0 0 1

Hash Anti Join (cost=1.36..4.23 rows=1 width=61) (actual time=0.023..0.023 rows=0 loops=1)

  • Output: NULL::integer, av_2.se_id, rtpe_1.id, av_2.re_id, av_2.re2_id, NULL::integer, av_2.value_num, NULL::text
  • Hash Cond: ((rtpe_1.id = av_1_1.pe_id) AND (av_2.se_id = av_1_1.se_id) AND (av_2.ad_id = av_1_1.ad_id) AND (av_2.re_id = av_1_1.re_id) AND (av_2.re2_id = av_1_1.re2_id))
  • Buffers: shared hit=2
30. 0.005 0.022 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.58 rows=9 width=25) (actual time=0.022..0.022 rows=0 loops=1)

  • Output: av_2.se_id, av_2.re_id, av_2.re2_id, av_2.value_num, av_2.ad_id, rtpe_1.id
  • Join Filter: (av_2.pe_id = rtpe_1.baseline_id)
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=2
31. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on hansandersplanner.rtpe rtpe_1 (cost=0.00..1.34 rows=1 width=8) (actual time=0.008..0.011 rows=1 loops=1)

  • Output: rtpe_1.id, rtpe_1.name, rtpe_1.is_default, rtpe_1.baseline_id, rtpe_1.sortkey, rtpe_1.user_id
  • Filter: (rtpe_1.id = 3)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
32. 0.006 0.006 ↑ 1.0 9 1

Seq Scan on hansandersplanner.rtseperereav av_2 (cost=0.00..1.14 rows=9 width=25) (actual time=0.004..0.006 rows=9 loops=1)

  • Output: av_2.id, av_2.se_id, av_2.pe_id, av_2.re_id, av_2.re2_id, av_2.ad_id, av_2.value_num, av_2.value_text
  • Filter: ((av_2.se_id = 3025) AND (av_2.ad_id = 76))
  • Buffers: shared hit=1
33. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.16..1.16 rows=9 width=20) (never executed)

  • Output: av_1_1.pe_id, av_1_1.se_id, av_1_1.re_id, av_1_1.re2_id, av_1_1.ad_id
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on hansandersplanner.rtseperereav av_1_1 (cost=0.00..1.16 rows=9 width=20) (never executed)

  • Output: av_1_1.pe_id, av_1_1.se_id, av_1_1.re_id, av_1_1.re2_id, av_1_1.ad_id
  • Filter: ((av_1_1.pe_id = 3) AND (av_1_1.se_id = 3025) AND (av_1_1.ad_id = 76))
35. 42,876.926 42,876.926 ↓ 10.2 1,867,326 1

CTE Scan on z (cost=0.00..3,663.68 rows=183,184 width=56) (actual time=72.658..42,876.926 rows=1,867,326 loops=1)

  • Output: z.se_id, z.pe_id, z.zo_id, z.co_id, z.sh_id, 78, z.attraction
  • Buffers: shared hit=164 read=16529
36. 692.628 692.628 ↓ 10.2 1,867,326 1

CTE Scan on z z_1 (cost=0.00..3,663.68 rows=183,184 width=56) (actual time=0.003..692.628 rows=1,867,326 loops=1)

  • Output: z_1.se_id, z_1.pe_id, z_1.zo_id, z_1.co_id, z_1.sh_id, 80, z_1.nd_attraction
37. 629.186 43,084.308 ↓ 10.2 3,734,652 1

Append (cost=168,795.13..177,954.33 rows=366,368 width=56) (actual time=57.021..43,084.308 rows=3,734,652 loops=1)

  • Buffers: shared hit=1189 read=15498
38.          

CTE z

39. 37,284.903 39,680.973 ↓ 10.2 1,867,326 1

Hash Join (cost=5,600.83..168,795.13 rows=183,184 width=84) (actual time=57.014..39,680.973 rows=1,867,326 loops=1)

  • Output: 3025, 3, x.zo_id, x.co_id, sh.sh_id, ((((((COALESCE(rtsepezobrav.value_num, '1'::numeric) * rtseperereav.value_num) * n.competitive_strength))::double precision * CASE n.shop_concept WHEN '5'::numeric THEN (((('1'::numeric * CASE WHEN (n.comp_strength = '1'::numeric) THEN 0.7 ELSE CASE WHEN (n.comp_strength = '2'::numeric) THEN '1'::numeric ELSE CASE WHEN (n.comp_strength = '3'::numeric) THEN 1.3 ELSE '1'::numeric END END END) * '1'::numeric))::double precision / exp((((x.value_num * sh.ddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.599999999999999978'::double precision)))) WHEN '2'::numeric THEN (((((((((('1'::numeric * CASE WHEN (n.accessibility = '2'::numeric) THEN 1.18 ELSE CASE WHEN (n.accessibility = '0'::numeric) THEN 0.9 ELSE '1'::numeric END END) * CASE WHEN (n.visibility = '1'::numeric) THEN 0.8 ELSE CASE WHEN (n.visibility = '2'::numeric) THEN 0.9 ELSE CASE WHEN (n.visibility = '4'::numeric) THEN 1.03 ELSE CASE WHEN (n.visibility = '5'::numeric) THEN 1.05 ELSE '1'::numeric END END END END) * ('1'::numeric + ((n.passage - '3'::numeric) * 0.05))) * CASE WHEN (n.age < '5'::numeric) THEN 0.85 ELSE CASE WHEN (n.age > '15'::numeric) THEN 1.1 ELSE '1'::numeric END END) * CASE WHEN (n.cora = '1'::numeric) THEN CASE WHEN (n.re_id = 223341) THEN 1.6 ELSE 1.3 END ELSE '1'::numeric END) * CASE WHEN (n.concept = '1'::numeric) THEN 0.95 ELSE '1'::numeric END) * CASE WHEN (n.audio = '1'::numeric) THEN 1.05 ELSE '1'::numeric END) * CASE WHEN (n.re_id = 223343) THEN '1'::numeric ELSE CASE WHEN (n.re_id = 223342) THEN 0.7 ELSE CASE WHEN (n.re_id = 223341) THEN 0.88 ELSE '1'::numeric END END END) / exp(((((((x.value_num * sh.ddp) * GREATEST(exp(((- n.pn_other) / '40'::numeric)), 0.7)) * CASE WHEN (n.re_id = 223342) THEN 0.95 ELSE '1'::numeric END) * CASE WHEN (n.re_id = 223341) THEN 1.07 ELSE '1'::numeric END) * CASE WHEN (n.type = '1'::numeric) THEN 0.9 ELSE '1'::numeric END) * CASE WHEN (n.type = '3'::numeric) THEN 1.1 ELSE '1'::numeric END))))::double precision WHEN '4'::numeric THEN ('1'::double precision / exp((((x.value_num * sh.ddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.599999999999999978'::double precision)))) WHEN '3'::numeric THEN ('1'::double precision / exp((((x.value_num * sh.ddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.599999999999999978'::double precision)))) ELSE NULL::double precision END) * (n.fc)::double precision))::numeric, ((((((COALESCE(rtsepezobrav.value_num, '1'::numeric) * rtseperereav.value_num) * n.competitive_strength))::double precision * CASE n.shop_concept WHEN '5'::numeric THEN (((('1'::numeric * CASE WHEN (n.comp_strength = '1'::numeric) THEN 0.7 ELSE CASE WHEN (n.comp_strength = '2'::numeric) THEN '1'::numeric ELSE CASE WHEN (n.comp_strength = '3'::numeric) THEN 1.3 ELSE '1'::numeric END END END) * 0.001))::double precision / exp((((x.value_num * sh.nddp))::double precision + (((n.pn_other)::double precision ^ '1.19999999999999996'::double precision) * '0.00200000000000000004'::double precision)))) WHEN '2'::numeric THEN ((((('1'::numeric * ('1'::numeric + ((n.passage - '3'::numeric) * 0.05))) * CASE WHEN (n.cora = '1'::numeric) THEN 1.3 ELSE '1'::numeric END))::double precision * LEAST('0.0500000000000000028'::double precision, (((n.pn_other)::double precision ^ '1.89999999999999991'::double precision) * '0.00100000000000000002'::double precision))) / exp(((((x.value_num * sh.nddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.699999999999999956'::double precision)) * (CASE WHEN (n.re_id = 223342) THEN 0.8 ELSE '1'::numeric END)::double precision))) WHEN '4'::numeric THEN (('1'::double precision * LEAST('0.0500000000000000028'::double precision, (((n.pn_other)::double precision ^ '1.89999999999999991'::double precision) * '0.00100000000000000002'::double precision))) / exp((((x.value_num * sh.nddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.699999999999999956'::double precision)))) WHEN '3'::numeric THEN (('1'::double precision * LEAST('0.0500000000000000028'::double precision, (((n.pn_other)::double precision ^ '1.89999999999999991'::double precision) * '0.00100000000000000002'::double precision))) / exp((((x.value_num * sh.nddp))::double precision * GREATEST(exp(((- (n.pn_other)::double precision) / '40'::double precision)), '0.699999999999999956'::double precision)))) ELSE NULL::double precision END) * (n.fc)::double precision))::numeric
  • Hash Cond: ((x.co_id = n.co_id) AND (z_2.re_id = rtseperereav.re2_id))
  • Buffers: shared hit=1189 read=15498
40. 1,614.134 2,356.691 ↑ 1.0 1,869,614 1

Hash Join (cost=1,550.46..71,028.13 rows=1,870,905 width=32) (actual time=17.487..2,356.691 rows=1,869,614 loops=1)

  • Output: x.zo_id, x.co_id, x.value_num, x.pe_id, z_2.se_id, z_2.re_id
  • Hash Cond: (x.zo_id = z_2.zo_id)
  • Buffers: shared hit=861 read=15498
41. 727.343 727.343 ↑ 1.0 1,869,614 1

Seq Scan on hansandersplanner.rtpezocoav x (cost=0.00..43,752.72 rows=1,870,905 width=24) (actual time=2.251..727.343 rows=1,869,614 loops=1)

  • Output: x.id, x.ad_id, x.pe_id, x.zo_id, x.co_id, x.value_num, x.value_text
  • Filter: ((x.pe_id = 3) AND (x.ad_id = 6))
  • Rows Removed by Filter: 11701
  • Buffers: shared hit=35 read=15498
42. 6.881 15.214 ↑ 1.3 19,782 1

Hash (cost=1,221.16..1,221.16 rows=26,344 width=12) (actual time=15.214..15.214 rows=19,782 loops=1)

  • Output: z_2.zo_id, z_2.se_id, z_2.re_id
  • Buckets: 32768 Batches: 1 Memory Usage: 1107kB
  • Buffers: shared hit=826
43. 8.333 8.333 ↑ 1.3 19,782 1

Seq Scan on hansandersplanner.calc_zones z_2 (cost=0.00..1,221.16 rows=26,344 width=12) (actual time=0.008..8.333 rows=19,782 loops=1)

  • Output: z_2.zo_id, z_2.se_id, z_2.re_id
  • Filter: ((z_2.pe_id = 3) AND (z_2.se_id = 3025))
  • Buffers: shared hit=826
44. 5.423 39.379 ↓ 3.0 5,649 1

Hash (cost=4,022.38..4,022.38 rows=1,866 width=104) (actual time=39.379..39.379 rows=5,649 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, sh.sh_id, sh.ddp, sh.nddp, rtsepezobrav.value_num, rtseperereav.value_num, rtseperereav.re2_id
  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 681kB
  • Buffers: shared hit=328
45. 2.714 33.956 ↓ 3.0 5,649 1

Hash Join (cost=103.98..4,022.38 rows=1,866 width=104) (actual time=3.318..33.956 rows=5,649 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, sh.sh_id, sh.ddp, sh.nddp, rtsepezobrav.value_num, rtseperereav.value_num, rtseperereav.re2_id
  • Hash Cond: (n.re_id = rtseperereav.re_id)
  • Buffers: shared hit=328
46. 1.695 31.189 ↓ 1.0 1,883 1

Hash Join (cost=98.42..3,991.15 rows=1,866 width=95) (actual time=3.256..31.189 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, sh.sh_id, sh.ddp, sh.nddp, rtsepezobrav.value_num
  • Hash Cond: (n.shop_concept = (sh.sh_id)::numeric)
  • Buffers: shared hit=325
47. 7.716 29.460 ↑ 1.0 1,883 1

Hash Right Join (cost=97.25..3,961.91 rows=1,883 width=81) (actual time=3.189..29.460 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, rtsepezobrav.value_num
  • Hash Cond: ((rtsepezobrav.se_id = n.se_id) AND (rtsepezobrav.pe_id = n.pe_id) AND (rtsepezobrav.zo_id = n.zo_id) AND (rtsepezobrav.br_id = n.br_id))
  • Buffers: shared hit=324
48. 2.807 18.605 ↑ 1.0 19,783 1

Append (cost=0.00..2,577.01 rows=19,784 width=21) (actual time=0.019..18.605 rows=19,783 loops=1)

  • Buffers: shared hit=293
49. 7.626 7.626 ↑ 1.0 19,783 1

Seq Scan on hansandersplanner.rtsepezobrav (cost=0.00..492.20 rows=19,783 width=21) (actual time=0.018..7.626 rows=19,783 loops=1)

  • Output: rtsepezobrav.value_num, rtsepezobrav.se_id, rtsepezobrav.pe_id, rtsepezobrav.zo_id, rtsepezobrav.br_id
  • Filter: ((rtsepezobrav.se_id = 3025) AND (rtsepezobrav.pe_id = 3) AND (rtsepezobrav.ad_id = 75))
  • Buffers: shared hit=146
50. 0.001 8.172 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=937.32..1,985.89 rows=1 width=21) (actual time=8.172..8.172 rows=0 loops=1)

  • Output: "*SELECT* 2".value_num, "*SELECT* 2".se_id, "*SELECT* 2".pe_id, "*SELECT* 2".zo_id, "*SELECT* 2".br_id
  • Buffers: shared hit=147
51. 0.002 8.171 ↓ 0.0 0 1

Hash Anti Join (cost=937.32..1,985.88 rows=1 width=61) (actual time=8.171..8.171 rows=0 loops=1)

  • Output: NULL::integer, av.se_id, rtpe.id, av.zo_id, av.br_id, NULL::integer, av.value_num, NULL::text
  • 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.zo_id = av_1.zo_id) AND (av.br_id = av_1.br_id))
  • Buffers: shared hit=147
52. 2.636 8.169 ↓ 0.0 0 1

Nested Loop (cost=0.00..691.37 rows=19,050 width=25) (actual time=8.169..8.169 rows=0 loops=1)

  • Output: av.se_id, av.zo_id, av.br_id, av.value_num, av.ad_id, rtpe.id
  • Join Filter: (av.pe_id = rtpe.baseline_id)
  • Rows Removed by Join Filter: 19783
  • Buffers: shared hit=147
53. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on hansandersplanner.rtpe (cost=0.00..1.34 rows=1 width=8) (actual time=0.013..0.017 rows=1 loops=1)

  • Output: rtpe.id, rtpe.name, rtpe.is_default, rtpe.baseline_id, rtpe.sortkey, rtpe.user_id
  • Filter: (rtpe.id = 3)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
54. 5.516 5.516 ↑ 1.0 19,783 1

Seq Scan on hansandersplanner.rtsepezobrav av (cost=0.00..442.75 rows=19,783 width=25) (actual time=0.009..5.516 rows=19,783 loops=1)

  • Output: av.id, av.se_id, av.pe_id, av.zo_id, av.br_id, av.ad_id, av.value_num, av.value_text
  • Filter: ((av.se_id = 3025) AND (av.ad_id = 75))
  • Buffers: shared hit=146
55. 0.000 0.000 ↓ 0.0 0

Hash (cost=492.20..492.20 rows=19,783 width=20) (never executed)

  • Output: av_1.pe_id, av_1.se_id, av_1.zo_id, av_1.br_id, av_1.ad_id
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on hansandersplanner.rtsepezobrav av_1 (cost=0.00..492.20 rows=19,783 width=20) (never executed)

  • Output: av_1.pe_id, av_1.se_id, av_1.zo_id, av_1.br_id, av_1.ad_id
  • Filter: ((av_1.pe_id = 3) AND (av_1.se_id = 3025) AND (av_1.ad_id = 75))
57. 1.756 3.139 ↑ 1.0 1,883 1

Hash (cost=59.59..59.59 rows=1,883 width=84) (actual time=3.139..3.139 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, n.zo_id, n.br_id
  • Buckets: 2048 Batches: 1 Memory Usage: 192kB
  • Buffers: shared hit=31
58. 1.383 1.383 ↑ 1.0 1,883 1

Seq Scan on hansandersplanner.calc_nodes n (cost=0.00..59.59 rows=1,883 width=84) (actual time=0.010..1.383 rows=1,883 loops=1)

  • Output: n.competitive_strength, n.shop_concept, n.comp_strength, n.pn_other, n.accessibility, n.visibility, n.passage, n.age, n.cora, n.re_id, n.concept, n.audio, n.type, n.fc, n.co_id, n.pe_id, n.se_id, n.zo_id, n.br_id
  • Filter: ((n.pe_id = 3) AND (n.se_id = 3025))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=31
59. 0.023 0.034 ↑ 1.0 6 1

Hash (cost=1.09..1.09 rows=6 width=18) (actual time=0.034..0.034 rows=6 loops=1)

  • Output: sh.sh_id, sh.ddp, sh.nddp, sh.se_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
60. 0.011 0.011 ↑ 1.0 6 1

Seq Scan on hansandersplanner.calc_shopconcepts sh (cost=0.00..1.09 rows=6 width=18) (actual time=0.008..0.011 rows=6 loops=1)

  • Output: sh.sh_id, sh.ddp, sh.nddp, sh.se_id
  • Filter: ((sh.pe_id = 3) AND (sh.se_id = 3025))
  • Buffers: shared hit=1
61. 0.009 0.053 ↑ 1.1 9 1

Hash (cost=5.44..5.44 rows=10 width=21) (actual time=0.053..0.053 rows=9 loops=1)

  • Output: rtseperereav.value_num, rtseperereav.se_id, rtseperereav.pe_id, rtseperereav.re_id, rtseperereav.re2_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
62. 0.003 0.044 ↑ 1.1 9 1

Append (cost=0.00..5.44 rows=10 width=21) (actual time=0.016..0.044 rows=9 loops=1)

  • Buffers: shared hit=3
63. 0.019 0.019 ↑ 1.0 9 1

Seq Scan on hansandersplanner.rtseperereav (cost=0.00..1.16 rows=9 width=21) (actual time=0.015..0.019 rows=9 loops=1)

  • Output: rtseperereav.value_num, rtseperereav.se_id, rtseperereav.pe_id, rtseperereav.re_id, rtseperereav.re2_id
  • Filter: ((rtseperereav.pe_id = 3) AND (rtseperereav.se_id = 3025) AND (rtseperereav.ad_id = 76))
  • Buffers: shared hit=1
64. 0.000 0.022 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=1.36..4.24 rows=1 width=21) (actual time=0.022..0.022 rows=0 loops=1)

  • Output: "*SELECT* 2_1".value_num, "*SELECT* 2_1".se_id, "*SELECT* 2_1".pe_id, "*SELECT* 2_1".re_id, "*SELECT* 2_1".re2_id
  • Buffers: shared hit=2
65. 0.001 0.022 ↓ 0.0 0 1

Hash Anti Join (cost=1.36..4.23 rows=1 width=61) (actual time=0.021..0.022 rows=0 loops=1)

  • Output: NULL::integer, av_2.se_id, rtpe_1.id, av_2.re_id, av_2.re2_id, NULL::integer, av_2.value_num, NULL::text
  • Hash Cond: ((rtpe_1.id = av_1_1.pe_id) AND (av_2.se_id = av_1_1.se_id) AND (av_2.ad_id = av_1_1.ad_id) AND (av_2.re_id = av_1_1.re_id) AND (av_2.re2_id = av_1_1.re2_id))
  • Buffers: shared hit=2
66. 0.005 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.58 rows=9 width=25) (actual time=0.021..0.021 rows=0 loops=1)

  • Output: av_2.se_id, av_2.re_id, av_2.re2_id, av_2.value_num, av_2.ad_id, rtpe_1.id
  • Join Filter: (av_2.pe_id = rtpe_1.baseline_id)
  • Rows Removed by Join Filter: 9
  • Buffers: shared hit=2
67. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on hansandersplanner.rtpe rtpe_1 (cost=0.00..1.34 rows=1 width=8) (actual time=0.007..0.010 rows=1 loops=1)

  • Output: rtpe_1.id, rtpe_1.name, rtpe_1.is_default, rtpe_1.baseline_id, rtpe_1.sortkey, rtpe_1.user_id
  • Filter: (rtpe_1.id = 3)
  • Rows Removed by Filter: 26
  • Buffers: shared hit=1
68. 0.006 0.006 ↑ 1.0 9 1

Seq Scan on hansandersplanner.rtseperereav av_2 (cost=0.00..1.14 rows=9 width=25) (actual time=0.004..0.006 rows=9 loops=1)

  • Output: av_2.id, av_2.se_id, av_2.pe_id, av_2.re_id, av_2.re2_id, av_2.ad_id, av_2.value_num, av_2.value_text
  • Filter: ((av_2.se_id = 3025) AND (av_2.ad_id = 76))
  • Buffers: shared hit=1
69. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.16..1.16 rows=9 width=20) (never executed)

  • Output: av_1_1.pe_id, av_1_1.se_id, av_1_1.re_id, av_1_1.re2_id, av_1_1.ad_id
70. 0.000 0.000 ↓ 0.0 0

Seq Scan on hansandersplanner.rtseperereav av_1_1 (cost=0.00..1.16 rows=9 width=20) (never executed)

  • Output: av_1_1.pe_id, av_1_1.se_id, av_1_1.re_id, av_1_1.re2_id, av_1_1.ad_id
  • Filter: ((av_1_1.pe_id = 3) AND (av_1_1.se_id = 3025) AND (av_1_1.ad_id = 76))
71. 41,887.604 41,887.604 ↓ 10.2 1,867,326 1

CTE Scan on z (cost=0.00..3,663.68 rows=183,184 width=56) (actual time=57.020..41,887.604 rows=1,867,326 loops=1)

  • Output: z.se_id, z.pe_id, z.zo_id, z.co_id, z.sh_id, 78, z.attraction
  • Buffers: shared hit=1189 read=15498
72. 567.518 567.518 ↓ 10.2 1,867,326 1

CTE Scan on z z_1 (cost=0.00..3,663.68 rows=183,184 width=56) (actual time=0.002..567.518 rows=1,867,326 loops=1)

  • Output: z_1.se_id, z_1.pe_id, z_1.zo_id, z_1.co_id, z_1.sh_id, 80, z_1.nd_attraction