explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vnWQ

Settings
# exclusive inclusive rows x rows loops node
1. 9,900.468 13,206.961 ↓ 0.0 0 1

Insert on beobank19_predict.rtsepezocoav_p_452_119025 citus_table_alias (cost=20,152.26..20,152.35 rows=2 width=68) (actual time=13,206.933..13,206.961 rows=0 loops=1)

2. 53.560 3,306.493 ↓ 53,967.5 107,935 1

Subquery Scan on *SELECT* (cost=20,152.26..20,152.35 rows=2 width=68) (actual time=2,937.495..3,306.493 rows=107,935 loops=1)

  • Output: NULL::bigint, "*SELECT*"."?column?", "*SELECT*"."?column?_1", "*SELECT*".zoneid, "*SELECT*".nodeid, "*SELECT*".allocation, NULL::character varying, "*SELECT*"."?column?_2
3. 292.150 3,252.933 ↓ 53,967.5 107,935 1

WindowAgg (cost=20,152.26..20,152.32 rows=2 width=24) (actual time=2,937.493..3,252.933 rows=107,935 loops=1)

  • Output: 12, 452, c.zoneid, c.nodeid, (((z.mp * c.pa_ndpa) / ((sum(c.pa_ndpa) OVER (?) + ('{0}'::double precision[])[c.ct_id]) + (('{0}'::double precision[])[c.ct_id] * z.inh_sqkm))))::integer, 335
4. 38.815 2,960.783 ↓ 53,967.5 107,935 1

Sort (cost=20,152.26..20,152.26 rows=2 width=36) (actual time=2,937.381..2,960.783 rows=107,935 loops=1)

  • Output: c.zoneid, c.nodeid, z.mp, c.pa_ndpa, c.ct_id, z.inh_sqkm
  • Sort Key: c.zoneid
  • Sort Method: quicksort Memory: 11,505kB
5. 11.972 2,921.968 ↓ 53,967.5 107,935 1

Hash Join (cost=20,075.36..20,152.25 rows=2 width=36) (actual time=2,909.905..2,921.968 rows=107,935 loops=1)

  • Output: c.zoneid, c.nodeid, z.mp, c.pa_ndpa, c.ct_id, z.inh_sqkm
  • Hash Cond: (z.zo_id = c.zoneid)
6. 0.092 0.160 ↑ 1.7 476 1

Bitmap Heap Scan on beobank19_predict.calc_zones_115705 z (cost=24.41..98.30 rows=793 width=20) (actual time=0.081..0.160 rows=476 loops=1)

  • Output: z.zo_id, z.se_id, z.pe_id, z.re_id, z.ct_id, z.sqkm, z.inh_sqkm, z.fam, z.secres, z.inh, z.fte, z.rev_wealthy, z.nr_rich_people, z.mp_before_corr, z.mp, z.income_net, z.comp_s_r, z.mp2, z.avg_age, z.nr_indep, z.werkloosheidsgraad
  • Recheck Cond: ((z.pe_id = 391) AND (z.se_id = 12))
  • Heap Blocks: exact=11
7. 0.068 0.068 ↑ 1.7 476 1

Bitmap Index Scan on idx_calc_zones_sepe_115705 (cost=0.00..24.21 rows=793 width=0) (actual time=0.067..0.068 rows=476 loops=1)

  • Index Cond: ((z.pe_id = 391) AND (z.se_id = 12))
8. 35.198 2,909.836 ↓ 107,935.0 107,935 1

Hash (cost=20,050.94..20,050.94 rows=1 width=20) (actual time=2,909.813..2,909.836 rows=107,935 loops=1)

  • Output: c.zoneid, c.nodeid, c.pa_ndpa, c.ct_id
  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 6,506kB
9. 46.736 2,874.638 ↓ 107,935.0 107,935 1

Subquery Scan on c (cost=20,049.45..20,050.94 rows=1 width=20) (actual time=2,653.165..2,874.638 rows=107,935 loops=1)

  • Output: c.zoneid, c.nodeid, c.pa_ndpa, c.ct_id
  • Filter: ((worker_hash(c.zoneid) >= '-2147483648'::integer) AND (worker_hash(c.zoneid) <= '-2058005164'::integer))
10. 166.324 2,827.902 ↓ 5,996.4 107,935 1

WindowAgg (cost=20,049.45..20,050.58 rows=18 width=28) (actual time=2,653.161..2,827.902 rows=107,935 loops=1)

  • Output: f.nodeid, f.zoneid, NULL::integer, f.ct_id, (((f.attraction * f.penalty) * ('1'::double precision - (('1'::double precision - CASE WHEN (sum((f.attraction * f.penalty)) OVER (?) = '0'::double precision) THEN '0'::double precision ELSE (max((f.attraction * f.penalty)) OVER (?) / sum((f.attraction * f.penalty)) OVER (?)) END) * ('1'::double precision - (('{{0.8,0.9}}'::double precision[])[f.ct_id][f.shopconcept] ^ ('1.2'::double precision * (exp((('{{2,2}}'::double precision[])[f.ct_id][f.shopconcept] * ('{{0.175,0.2}}'::double precision[])[f.ct_id][f.shopconcept])) - '1'::double precision))))))) + f.nd_attraction), f.ownerid
11. 2,661.578 2,661.578 ↓ 5,996.4 107,935 1

Sort (cost=20,049.45..20,049.50 rows=18 width=44) (actual time=2,653.142..2,661.578 rows=107,935 loops=1)

  • Output: f.zoneid, f.ownerid, f.nodeid, f.ct_id, f.attraction, f.penalty, f.shopconcept, f.nd_attraction