explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B92

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 57,653.226 ↑ 1.0 50 1

Limit (cost=2,946,899.16..2,946,899.28 rows=50 width=44) (actual time=57,653.217..57,653.226 rows=50 loops=1)

2. 2.692 57,653.217 ↑ 101.8 100 1

Sort (cost=2,946,899.03..2,946,924.48 rows=10,181 width=44) (actual time=57,653.210..57,653.217 rows=100 loops=1)

  • Sort Key: (max(timeblock0_.start_date))
  • Sort Method: top-N heapsort Memory: 33kB
3. 8,106.557 57,650.525 ↓ 1.8 18,643 1

HashAggregate (cost=2,946,357.21..2,946,509.92 rows=10,181 width=44) (actual time=57,643.146..57,650.525 rows=18,643 loops=1)

  • Group Key: location1_.corporation_id
4. 10,656.999 49,543.968 ↓ 20.6 36,473,996 1

Hash Right Join (cost=335,845.52..2,933,082.03 rows=1,770,023 width=12) (actual time=6,674.631..49,543.968 rows=36,473,996 loops=1)

  • Hash Cond: (timeblock0_.location_id = location1_.id)
5. 33,167.426 38,762.116 ↓ 1.8 37,164,435 1

Bitmap Heap Scan on timeblock timeblock0_ (cost=319,343.54..2,821,918.06 rows=20,523,081 width=12) (actual time=6,549.678..38,762.116 rows=37,164,435 loops=1)

  • Recheck Cond: (deleted = 'N'::bpchar)
  • Filter: (date_published IS NOT NULL)
  • Rows Removed by Filter: 3398609
  • Heap Blocks: exact=1951763
6. 5,594.690 5,594.690 ↑ 1.0 40,563,178 1

Bitmap Index Scan on i_timeblock_start (cost=0.00..314,212.77 rows=40,597,962 width=0) (actual time=5,594.690..5,594.690 rows=40,563,178 loops=1)

7. 5.813 124.853 ↓ 2.0 22,760 1

Hash (cost=16,361.05..16,361.05 rows=11,275 width=16) (actual time=124.853..124.853 rows=22,760 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1323kB
8. 8.458 119.040 ↓ 2.0 22,760 1

Hash Join (cost=8,390.54..16,361.05 rows=11,275 width=16) (actual time=47.643..119.040 rows=22,760 loops=1)

  • Hash Cond: (location1_.corporation_id = corporation.id)
9. 27.148 89.908 ↑ 1.0 22,850 1

Hash Join (cost=5,485.24..13,164.00 rows=23,109 width=16) (actual time=26.785..89.908 rows=22,850 loops=1)

  • Hash Cond: (location1_.id = location.id)
10. 36.244 36.244 ↑ 1.0 130,682 1

Seq Scan on location location1_ (cost=0.00..6,028.30 rows=130,730 width=16) (actual time=0.025..36.244 rows=130,682 loops=1)

11. 4.155 26.516 ↑ 1.0 22,850 1

Hash (cost=5,196.38..5,196.38 rows=23,109 width=8) (actual time=26.516..26.516 rows=22,850 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1149kB
12. 17.972 22.361 ↑ 1.0 22,850 1

Bitmap Heap Scan on location (cost=186.51..5,196.38 rows=23,109 width=8) (actual time=4.720..22.361 rows=22,850 loops=1)

  • Recheck Cond: (deleted = 'N'::bpchar)
  • Heap Blocks: exact=2659
13. 4.389 4.389 ↑ 1.0 22,850 1

Bitmap Index Scan on idx_location_id_del (cost=0.00..180.74 rows=23,109 width=0) (actual time=4.389..4.389 rows=22,850 loops=1)

14. 4.204 20.674 ↓ 1.0 24,164 1

Hash (cost=2,604.15..2,604.15 rows=24,092 width=8) (actual time=20.674..20.674 rows=24,164 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1200kB
15. 13.974 16.470 ↓ 1.0 24,164 1

Bitmap Heap Scan on corporation (cost=254.00..2,604.15 rows=24,092 width=8) (actual time=2.695..16.470 rows=24,164 loops=1)

  • Recheck Cond: (deleted = 'N'::bpchar)
  • Heap Blocks: exact=1710
16. 2.496 2.496 ↓ 1.0 24,199 1

Bitmap Index Scan on corporation_del (cost=0.00..247.98 rows=24,092 width=0) (actual time=2.496..2.496 rows=24,199 loops=1)

  • Index Cond: (deleted = 'N'::bpchar)