explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IaHY

Settings
# exclusive inclusive rows x rows loops node
1. 22,473.053 139,424.792 ↓ 472.6 18,903,389 1

GroupAggregate (cost=15,704,754.21..16,548,472.75 rows=40,000 width=242) (actual time=112,346.850..139,424.792 rows=18,903,389 loops=1)

  • Group Key: gen_lot_last_surface_by_category.lot_id, gen_lot_last_surface_by_category.dgi_position
  • Buffers: shared hit=290433 read=49621, temp read=1430433 written=1431259
  • I/O Timings: read=182.389
2. 22,175.146 116,951.739 ↓ 1.3 18,920,669 1

Sort (cost=15,704,754.21..15,741,372.41 rows=14,647,279 width=209) (actual time=112,346.815..116,951.739 rows=18,920,669 loops=1)

  • Sort Key: gen_lot_last_surface_by_category.lot_id, gen_lot_last_surface_by_category.dgi_position
  • Sort Method: external merge Disk: 2313112kB
  • Buffers: shared hit=290433 read=49621, temp read=1430433 written=1431259
  • I/O Timings: read=182.389
3. 4,673.765 94,776.593 ↓ 1.3 18,920,669 1

Subquery Scan on gen_lot_last_surface_by_category (cost=10,151,985.24..10,957,585.59 rows=14,647,279 width=209) (actual time=78,799.503..94,776.593 rows=18,920,669 loops=1)

  • Buffers: shared hit=290433 read=49621, temp read=830593 written=831097
  • I/O Timings: read=182.389
4. 6,195.375 90,102.828 ↓ 1.3 18,920,669 1

Unique (cost=10,151,985.24..10,811,112.80 rows=14,647,279 width=209) (actual time=78,799.495..90,102.828 rows=18,920,669 loops=1)

  • Buffers: shared hit=290433 read=49621, temp read=830593 written=831097
  • I/O Timings: read=182.389
5. 26,163.348 83,907.453 ↓ 1.3 18,920,669 1

Sort (cost=10,151,985.24..10,188,603.44 rows=14,647,279 width=209) (actual time=78,799.492..83,907.453 rows=18,920,669 loops=1)

  • Sort Key: "*SELECT* 1".building_id, "*SELECT* 1".lot_id, (NULL::character varying), "*SELECT* 1".ref_measurement_type_id, "*SELECT* 1".estimated, "*SELECT* 1".value_entiere, ((0)::big
  • Sort Method: external merge Disk: 2280104kB
  • Buffers: shared hit=290433 read=49621, temp read=830593 written=831097
  • I/O Timings: read=182.389
6. 1,757.101 57,744.105 ↓ 1.3 18,920,669 1

Append (cost=515.75..5,404,816.63 rows=14,647,279 width=209) (actual time=2.689..57,744.105 rows=18,920,669 loops=1)

  • Buffers: shared hit=290433 read=49621, temp read=249822 written=250002
  • I/O Timings: read=182.389
7. 24.327 24.327 ↑ 1.2 17,986 1

Subquery Scan on *SELECT* 1 (cost=515.75..70,335.03 rows=22,089 width=150) (actual time=2.688..24.327 rows=17,986 loops=1)

  • Buffers: shared hit=7087
  • -> Bitmap Heap Scan on gen_lot_last_surface_by_category gen_lot_last_surface_by_category_1 (cost=515.75..70058.91 rows=22089 width=146) (actual time=2.687..21.759 rows=1
  • Recheck Cond: (ref_property_nature_id = 9)
  • Heap Blocks: exact=7014
  • Buffers: shared hit=7087
  • -> Bitmap Index Scan on gen_lot_last_surface_by_category_ref_property_nature_id_value_d (cost=0.00..510.23 rows=22089 width=0) (actual time=1.803..1.803 rows=17986
  • Index Cond: (ref_property_nature_id = 9)
  • Buffers: shared hit=73
8. 4,898.768 55,962.677 ↓ 1.3 18,902,683 1

Subquery Scan on *SELECT* 2 (cost=4,018,769.07..5,261,245.20 rows=14,625,190 width=209) (actual time=20,493.026..55,962.677 rows=18,902,683 loops=1)

  • Buffers: shared hit=283346 read=49621, temp read=249822 written=250002
  • I/O Timings: read=182.389
9. 25,459.496 51,063.909 ↓ 1.3 18,902,683 1

GroupAggregate (cost=4,018,769.07..5,078,430.33 rows=14,625,190 width=209) (actual time=20,493.022..51,063.909 rows=18,902,683 loops=1)

  • Group Key: gen_lot_last_surface_by_category_2.lot_id, gen_lot_last_surface_by_category_2.dgi_position
  • Buffers: shared hit=283346 read=49621, temp read=249822 written=250002
  • I/O Timings: read=182.389
10. 25,604.413 25,604.413 ↓ 1.0 18,921,625 1

Sort (cost=4,018,769.07..4,066,046.74 rows=18,911,069 width=48) (actual time=20,492.956..25,604.413 rows=18,921,625 loops=1)

  • Sort Key: gen_lot_last_surface_by_category_2.lot_id, gen_lot_last_surface_by_category_2.dgi_position
  • Sort Method: external merge Disk: 1196024kB
  • Buffers: shared hit=283346 read=49621, temp read=249822 written=250002
  • I/O Timings: read=182.389
  • -> Seq Scan on gen_lot_last_surface_by_category gen_lot_last_surface_by_category_2 (cost=0.00..569631.47 rows=18911069 width=48) (actual time=0.020..6986.340
  • Filter: (ref_property_nature_id <> 9)
  • Rows Removed by Filter: 17986
  • Buffers: shared hit=283346 read=49621
  • I/O Timings: read=182.389
Planning time : 0.543 ms
Execution time : 141,302.648 ms