explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6i4x

Settings
# exclusive inclusive rows x rows loops node
1. 598.217 53,309.928 ↑ 3,033.6 1,650 1

Subquery Scan on ss (cost=4,100,009.71..4,588,045.05 rows=5,005,491 width=17) (actual time=44,422.202..53,309.928 rows=1,650 loops=1)

  • Filter: (ss.pos < 4)
  • Rows Removed by Filter: 9862988
  • Buffers: shared hit=895139, temp read=102798 written=103016
  • rows=1827 loops=1)
2. 4,894.408 52,711.711 ↑ 1.5 9,864,638 1

WindowAgg (cost=4,100,009.71..4,400,339.15 rows=15,016,472 width=25) (actual time=44,422.200..52,711.711 rows=9,864,638 loops=1)

  • Buffers: shared hit=895139, temp read=102798 written=103016
3. 35,896.063 47,817.303 ↑ 1.5 9,864,638 1

Sort (cost=4,100,009.71..4,137,550.89 rows=15,016,472 width=17) (actual time=44,422.187..47,817.303 rows=9,864,638 loops=1)

  • Sort Key: uk_property_registry.district, uk_property_registry.price DESC
  • Sort Method: external merge Disk: 272856kB
  • Buffers: shared hit=895139, temp read=102798 written=103016
4. 6,968.423 11,921.240 ↑ 1.5 9,864,638 1

Hash Semi Join (cost=200.65..1,694,127.06 rows=15,016,472 width=17) (actual time=2,243.969..11,921.240 rows=9,864,638 loops=1)

  • Hash Cond: (uk_property_registry.transfer_date = uk_prop_date.transfer_date)
  • Buffers: shared hit=895139
5. 4,951.923 4,951.923 ↑ 1.0 50,036,022 1

Seq Scan on uk_property_registry (cost=0.00..1,395,510.00 rows=50,041,200 width=21) (actual time=0.020..4,951.923 rows=50,036,022 loops=1)

  • Buffers: shared hit=895098
6. 0.894 0.894 ↓ 1.0 1,827 1

Hash (cost=177.83..177.83 rows=1,826 width=4) (actual time=0.894..0.894 rows=1,827 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared hit=41
  • -> Seq Scan on uk_prop_date (cost=0.00..177.83 rows=1826 width=4) (actual time=0.007..0.702
  • Filter: ((transfer_date >= '2005-01-01'::date) AND (transfer_date <= '2010-01-01'::date))
  • Rows Removed by Filter: 7295
  • Buffers: shared hit=41
Planning time : 0.329 ms
Execution time : 53,363.490 ms