explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CZPx

Settings
# exclusive inclusive rows x rows loops node
1. 1,655.114 31,955.015 ↓ 2.9 210,533 1

Hash Join (cost=1,673,435.91..2,027,844.47 rows=73,026 width=47) (actual time=28,819.396..31,955.015 rows=210,533 loops=1)

  • Output: perf_1.base_date, perf_1.base_instrument, perf_1.base_price
  • Inner Unique: true
  • Hash Cond: ((perf_1.base_date = eomtable.eomdate) AND ((perf_1.base_instrument)::text = (eomtable.base_instrument)::text))
  • Buffers: shared hit=212405 read=238098, temp read=86909 written=86987
  • Execution Time: 32052.803 ms(36 lignes)
2. 1,481.735 1,481.735 ↑ 1.0 3,831,840 1

Seq Scan on folio.base_performance perf_1 (cost=0.00..263,565.40 rows=3,831,840 width=47) (actual time=0.030..1,481.735 rows=3,831,840 loops=1)

  • Output: perf_1.base_date, perf_1.base_instrument, perf_1.base_price
  • Buffers: shared hit=106214 read=119033
3. 70.626 28,818.166 ↑ 1.8 210,533 1

Hash (cost=1,664,320.15..1,664,320.15 rows=383,184 width=41) (actual time=28,818.165..28,818.166 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buckets: 262144 Batches: 4 Memory Usage: 5793kB
  • Buffers: shared hit=106191 read=119065, temp read=61926 written=63179
4. 28.074 28,747.540 ↑ 1.8 210,533 1

Subquery Scan on eomtable (cost=1,631,749.51..1,664,320.15 rows=383,184 width=41) (actual time=26,519.324..28,747.540 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buffers: shared hit=106191 read=119065, temp read=61926 written=62004
5. 451.779 28,719.466 ↑ 1.8 210,533 1

Unique (cost=1,631,749.51..1,660,488.31 rows=383,184 width=53) (actual time=26,519.319..28,719.466 rows=210,533 loops=1)

  • Output: (max(perf_2.base_date) OVER (?)), perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer)
  • Buffers: shared hit=106191 read=119065, temp read=61926 written=62004
6. 5,069.844 28,267.687 ↑ 1.0 3,831,840 1

Sort (cost=1,631,749.51..1,641,329.11 rows=3,831,840 width=53) (actual time=26,519.317..28,267.687 rows=3,831,840 loops=1)

  • Output: (max(perf_2.base_date) OVER (?)), perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer)
  • Sort Key: (max(perf_2.base_date) OVER (?)), perf_2.base_instrument
  • Sort Method: external merge Disk: 247704kB
  • Buffers: shared hit=106191 read=119065, temp read=61926 written=62004
7. 2,525.115 23,197.843 ↑ 1.0 3,831,840 1

WindowAgg (cost=899,759.45..1,081,771.85 rows=3,831,840 width=53) (actual time=16,536.657..23,197.843 rows=3,831,840 loops=1)

  • Output: max(perf_2.base_date) OVER (?), perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer)
  • Buffers: shared hit=106185 read=119065, temp read=30963 written=31002
8. 17,354.345 20,672.728 ↑ 1.0 3,831,840 1

Sort (cost=899,759.45..909,339.05 rows=3,831,840 width=53) (actual time=16,536.628..20,672.728 rows=3,831,840 loops=1)

  • Output: perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer), perf_2.base_date
  • Sort Key: perf_2.base_instrument, ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer) DESC
  • Sort Method: external merge Disk: 247704kB
  • Buffers: shared hit=106185 read=119065, temp read=30963 written=31002
9. 3,318.383 3,318.383 ↑ 1.0 3,831,840 1

Seq Scan on folio.base_performance perf_2 (cost=0.00..349,781.80 rows=3,831,840 width=53) (actual time=0.024..3,318.383 rows=3,831,840 loops=1)

  • Output: perf_2.base_instrument, (date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer, (date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer, (date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer, perf_2.base_date
  • Buffers: shared hit=106182 read=119065
Planning time : 1.281 ms