explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JISx

Settings
# exclusive inclusive rows x rows loops node
1. 2,169.390 3,659.910 ↑ 2.0 65,457 1

GroupAggregate (cost=54,741.90..59,419.51 rows=133,646 width=509) (actual time=1,110.299..3,659.910 rows=65,457 loops=1)

  • Group Key: loc.geo_longitude_nbr, loc.geo_latitude_nbr, p.proj_id, loc.geo_location_nme, p.cntry_code
  • Buffers: shared hit=21,043, temp read=19,953 written=19,953
2. 1,211.844 1,490.520 ↓ 1.5 203,924 1

Sort (cost=54,741.90..55,076.01 rows=133,646 width=509) (actual time=1,110.103..1,490.520 rows=203,924 loops=1)

  • Sort Key: loc.geo_longitude_nbr, loc.geo_latitude_nbr, p.proj_id, loc.geo_location_nme, p.cntry_code
  • Sort Method: external merge Disk: 104,560kB
  • Buffers: shared hit=20,273, temp read=19,953 written=19,953
3. 67.879 278.676 ↓ 1.5 203,924 1

Hash Join (cost=2,677.99..12,755.74 rows=133,646 width=509) (actual time=34.855..278.676 rows=203,924 loops=1)

  • Hash Cond: (p2s.sector_code = sec.sector_code)
  • Buffers: shared hit=20,273
4. 59.784 210.697 ↓ 1.5 203,924 1

Hash Join (cost=2,663.87..10,903.98 rows=133,646 width=470) (actual time=34.744..210.697 rows=203,924 loops=1)

  • Hash Cond: ((p.proj_id)::text = (p2s.proj_id)::text)
  • Buffers: shared hit=20,262
5. 47.821 133.528 ↑ 1.0 65,459 1

Hash Join (cost=1,301.56..7,223.26 rows=65,464 width=467) (actual time=16.886..133.528 rows=65,459 loops=1)

  • Hash Cond: (p2loc.geo_location_id = loc.geo_location_id)
  • Buffers: shared hit=19,765
6. 26.683 69.298 ↑ 1.0 65,468 1

Merge Join (cost=19.95..5,041.50 rows=65,468 width=451) (actual time=0.285..69.298 rows=65,468 loops=1)

  • Merge Cond: ((p.proj_id)::text = (p2loc.proj_id)::text)
  • Buffers: shared hit=19,183
7. 29.108 29.108 ↑ 1.0 18,838 1

Index Scan using project_pkey on project p (cost=0.29..1,881.62 rows=18,838 width=432) (actual time=0.021..29.108 rows=18,838 loops=1)

  • Buffers: shared hit=18,857
8. 13.507 13.507 ↑ 1.0 65,468 1

Index Only Scan using index_project2location on project2location p2loc (cost=0.41..2,294.44 rows=65,468 width=19) (actual time=0.022..13.507 rows=65,468 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=326
9. 7.700 16.409 ↓ 1.0 31,094 1

Hash (cost=892.95..892.95 rows=31,093 width=38) (actual time=16.409..16.409 rows=31,094 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,405kB
  • Buffers: shared hit=582
10. 8.709 8.709 ↓ 1.0 31,094 1

Seq Scan on project_location loc (cost=0.00..892.95 rows=31,093 width=38) (actual time=0.006..8.709 rows=31,094 loops=1)

  • Filter: ((geo_longitude_nbr IS NOT NULL) AND (geo_latitude_nbr IS NOT NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=582
11. 9.352 17.385 ↑ 1.0 38,458 1

Hash (cost=881.58..881.58 rows=38,458 width=19) (actual time=17.385..17.385 rows=38,458 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,428kB
  • Buffers: shared hit=497
12. 8.033 8.033 ↑ 1.0 38,458 1

Seq Scan on project2sector p2s (cost=0.00..881.58 rows=38,458 width=19) (actual time=0.005..8.033 rows=38,458 loops=1)

  • Buffers: shared hit=497
13. 0.061 0.100 ↑ 1.0 139 1

Hash (cost=12.39..12.39 rows=139 width=42) (actual time=0.100..0.100 rows=139 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=11
14. 0.039 0.039 ↑ 1.0 139 1

Seq Scan on project_sector sec (cost=0.00..12.39 rows=139 width=42) (actual time=0.003..0.039 rows=139 loops=1)

  • Buffers: shared hit=11