explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Pov

Settings
# exclusive inclusive rows x rows loops node
1. 187.304 10,389.308 ↓ 15.4 617,104 1

Unique (cost=1,148,409.07..1,157,598.23 rows=40,000 width=68) (actual time=9,199.075..10,389.308 rows=617,104 loops=1)

  • Buffers: shared hit=528 read=174,664, temp read=75,604 written=78,813
2. 3,024.891 10,202.004 ↑ 1.6 783,622 1

Sort (cost=1,148,409.07..1,151,472.12 rows=1,225,222 width=68) (actual time=9,199.072..10,202.004 rows=783,622 loops=1)

  • Sort Key: dcgi.city_term, dcgi.country_code
  • Sort Method: external merge Disk: 25,360kB
  • Buffers: shared hit=528 read=174,664, temp read=75,604 written=78,813
3. 100.924 7,177.113 ↑ 1.6 783,622 1

Subquery Scan on dcgi (cost=896,430.38..923,997.87 rows=1,225,222 width=68) (actual time=6,622.231..7,177.113 rows=783,622 loops=1)

  • Buffers: shared hit=528 read=174,664, temp read=71,497 written=74,687
4. 184.807 7,076.189 ↑ 1.6 783,622 1

Unique (cost=896,430.38..911,745.65 rows=1,225,222 width=76) (actual time=6,622.221..7,076.189 rows=783,622 loops=1)

  • Buffers: shared hit=528 read=174,664, temp read=71,497 written=74,687
5. 348.783 6,891.382 ↑ 1.6 783,653 1

Sort (cost=896,430.38..899,493.43 rows=1,225,222 width=76) (actual time=6,622.219..6,891.382 rows=783,653 loops=1)

  • Sort Key: ci.geoname_id, acn.city_term, ci.country_code, ci.population
  • Sort Method: external merge Disk: 34,056kB
  • Buffers: shared hit=528 read=174,664, temp read=71,497 written=74,687
6. 65.569 6,542.599 ↑ 1.6 783,653 1

Append (cost=633,015.63..663,647.18 rows=1,225,222 width=76) (actual time=5,905.893..6,542.599 rows=783,653 loops=1)

  • Buffers: shared hit=528 read=174,664, temp read=65,882 written=69,052
7. 226.406 6,476.985 ↑ 1.6 783,543 1

Unique (cost=633,015.63..645,266.75 rows=1,225,112 width=29) (actual time=5,905.892..6,476.985 rows=783,543 loops=1)

  • Buffers: shared hit=527 read=174,664, temp read=65,882 written=69,052
8. 1,009.293 6,250.579 ↑ 1.2 1,035,294 1

Sort (cost=633,015.63..636,078.41 rows=1,225,112 width=29) (actual time=5,905.89..6,250.579 rows=1,035,294 loops=1)

  • Sort Key: ci.geoname_id, acn.city_term, ci.country_code
  • Sort Method: external merge Disk: 44,320kB
  • Buffers: shared hit=527 read=174,664, temp read=65,882 written=69,052
9. 327.269 5,241.286 ↑ 1.2 1,035,294 1

Hash Join (cost=307,948.9..450,504.35 rows=1,225,112 width=29) (actual time=4,069.846..5,241.286 rows=1,035,294 loops=1)

  • Buffers: shared hit=527 read=174,664, temp read=57,501 written=60,644
10. 92.714 4,339.843 ↑ 1.0 807,348 1

Gather (cost=307,936.28..430,167.14 rows=807,348 width=29) (actual time=3,495.656..4,339.843 rows=807,348 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=523 read=174,664, temp read=57,501 written=60,644
11. 981.103 4,247.129 ↑ 1.2 269,116 3 / 3

Hash Join (cost=306,936.28..348,432.34 rows=336,395 width=29) (actual time=3,433.877..4,247.129 rows=269,116 loops=3)

  • Buffers: shared hit=523 read=174,664, temp read=57,501 written=60,644
12. 72.345 72.345 ↑ 1.2 269,116 3 / 3

Seq Scan on geo_lookup_alternate_city_name acn (cost=0..8,731.95 rows=336,395 width=19) (actual time=0.147..72.345 rows=269,116 loops=3)

  • Buffers: shared hit=289 read=5,079
13. 1,643.862 3,193.681 ↑ 1.3 4,007,984 3 / 3

Hash (cost=219,805.57..219,805.57 rows=5,012,457 width=14) (actual time=3,193.68..3,193.681 rows=4,007,984 loops=3)

  • Buffers: shared hit=96 read=169,585, temp written=54,500
14. 1,549.819 1,549.819 ↑ 1.3 4,007,984 3 / 3

Seq Scan on pm_lookup_location_geoname_city_info ci (cost=0..219,805.57 rows=5,012,457 width=14) (actual time=227.815..1,549.819 rows=4,007,984 loops=3)

  • Buffers: shared hit=96 read=169,585
15. 0.115 574.174 ↑ 1.0 383 1

Hash (cost=7.83..7.83 rows=383 width=3) (actual time=574.172..574.174 rows=383 loops=1)

  • Buffers: shared hit=4
16. 574.059 574.059 ↑ 1.0 383 1

Seq Scan on geo_lookup_location_country_language_iso lang (cost=0..7.83 rows=383 width=3) (actual time=573.958..574.059 rows=383 loops=1)

  • Buffers: shared hit=4
17. 0.045 0.045 ↑ 1.0 110 1

Seq Scan on pm_xref_fusion_airtable_city_term pm_xref_fusion_airtable_city_term (cost=0..2.1 rows=110 width=26) (actual time=0.033..0.045 rows=110 loops=1)

  • Buffers: shared hit=1
Planning time : 1.191 ms
Execution time : 10,445.197 ms