explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JnGm

Settings
# exclusive inclusive rows x rows loops node
1. 39,413.714 136,390.523 ↑ 1.1 27,301,021 1

WindowAgg (cost=10,007,579,636.27..10,008,300,021.50 rows=28,815,409 width=52) (actual time=82,476.435..136,390.523 rows=27,301,021 loops=1)

  • Output: m.id, m.property_id, count(r.meter_port_id) OVER (?), count(*) OVER (?), date_part('epoch'::text, (m.end_date - m.start_date)), r.migration_id
  • Buffers: shared hit=773 read=666002, temp read=1233812 written=1094997
2. 52,971.137 96,976.809 ↑ 1.1 27,301,021 1

Sort (cost=10,007,579,636.27..10,007,651,674.80 rows=28,815,409 width=52) (actual time=82,476.397..96,976.809 rows=27,301,021 loops=1)

  • Output: r.migration_id, m.id, m.property_id, r.meter_port_id, m.end_date, m.start_date
  • Sort Key: r.migration_id
  • Sort Method: external merge Disk: 1763136kB
  • Buffers: shared hit=773 read=666002, temp read=893797 written=894927
3. 28,847.613 44,005.672 ↑ 1.1 27,301,021 1

Hash Right Join (cost=10,000,000,829.30..10,001,054,648.02 rows=28,815,409 width=52) (actual time=1.387..44,005.672 rows=27,301,021 loops=1)

  • Output: r.migration_id, m.id, m.property_id, r.meter_port_id, m.end_date, m.start_date
  • Inner Unique: true
  • Hash Cond: (r.migration_id = m.id)
  • Buffers: shared hit=773 read=666002
4. 15,156.701 15,156.701 ↑ 1.0 30,659,972 1

Seq Scan on public.readings r (cost=10,000,000,000.00..10,000,972,936.56 rows=30,693,456 width=16) (actual time=0.013..15,156.701 rows=30,659,972 loops=1)

  • Output: r.meter_port_id, r.reading_date, r.reading_type, r.value, r.unit, r.service_type, r.estimation_type, r.medium_type, r.transport_type, r.transmitting_radio_article, r.transmitting_radio_serial, r.migration_id
  • Buffers: shared read=666002
5. 0.532 1.358 ↑ 1.1 956 1

Hash (cost=816.45..816.45 rows=1,028 width=36) (actual time=1.358..1.358 rows=956 loops=1)

  • Output: m.id, m.property_id, m.end_date, m.start_date
  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
  • Buffers: shared hit=773
6. 0.826 0.826 ↑ 1.1 956 1

Index Scan using pps_migrations_pkey on public.pps_migrations m (cost=0.28..816.45 rows=1,028 width=36) (actual time=0.008..0.826 rows=956 loops=1)

  • Output: m.id, m.property_id, m.end_date, m.start_date
  • Filter: ((m.migration_status)::text = 'COMPLETED'::text)
  • Rows Removed by Filter: 44
  • Buffers: shared hit=773