explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zt2w

Settings
# exclusive inclusive rows x rows loops node
1. 1,778.279 38,328.893 ↓ 2.9 210,533 1

Hash Join (cost=1,680,276.46..2,035,272.92 rows=73,692 width=47) (actual time=32,970.263..38,328.893 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=209808 read=240686, temp read=86909 written=86987
  • Execution Time: 38392.581 ms(36 lignes)
2. 3,582.141 3,582.141 ↑ 1.0 3,831,840 1

Seq Scan on folio.base_performance perf_1 (cost=0.00..263,739.76 rows=3,849,276 width=47) (actual time=0.009..3,582.141 rows=3,831,840 loops=1)

  • Output: perf_1.base_date, perf_1.base_instrument, perf_1.base_price
  • Buffers: shared hit=104806 read=120441
3. 71.313 32,968.473 ↑ 1.8 210,533 1

Hash (cost=1,671,118.54..1,671,118.54 rows=384,928 width=41) (actual time=32,968.473..32,968.473 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buckets: 262144 Batches: 4 Memory Usage: 5793kB
  • Buffers: shared hit=105002 read=120245, temp read=61926 written=63179
4. 27.460 32,897.160 ↑ 1.8 210,533 1

Subquery Scan on eomtable (cost=1,638,399.69..1,671,118.54 rows=384,928 width=41) (actual time=30,750.713..32,897.160 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buffers: shared hit=105002 read=120245, temp read=61926 written=62004
5. 430.592 32,869.700 ↑ 1.8 210,533 1

Unique (cost=1,638,399.69..1,667,269.26 rows=384,928 width=53) (actual time=30,750.707..32,869.700 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=105002 read=120245, temp read=61926 written=62004
6. 5,105.701 32,439.108 ↑ 1.0 3,831,840 1

Sort (cost=1,638,399.69..1,648,022.88 rows=3,849,276 width=53) (actual time=30,750.705..32,439.108 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=105002 read=120245, temp read=61926 written=62004
7. 2,560.927 27,333.407 ↑ 1.0 3,831,840 1

WindowAgg (cost=902,953.78..1,085,794.39 rows=3,849,276 width=53) (actual time=19,273.292..27,333.407 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=105002 read=120245, temp read=30963 written=31002
8. 18,700.705 24,772.480 ↑ 1.0 3,831,840 1

Sort (cost=902,953.78..912,576.97 rows=3,849,276 width=53) (actual time=19,273.270..24,772.480 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=105002 read=120245, temp read=30963 written=31002
9. 6,071.775 6,071.775 ↑ 1.0 3,831,840 1

Seq Scan on folio.base_performance perf_2 (cost=0.00..350,348.47 rows=3,849,276 width=53) (actual time=0.011..6,071.775 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=105002 read=120245
Planning time : 0.318 ms