explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G5p7

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

Unique (cost=1,156,781.07..1,165,970.23 rows=40,000 width=76) (actual time=9,781.782..10,945.76 rows=617,104 loops=1)

  • Buffers: shared hit=345 read=174,856, temp read=77,128 written=79,135
2. 3,126.744 10,787.622 ↑ 1.6 783,622 1

Sort (cost=1,156,781.07..1,159,844.12 rows=1,225,222 width=76) (actual time=9,781.78..10,787.622 rows=783,622 loops=1)

  • Sort Key: acn.city_term, ci.country_code, ci.population DESC
  • Sort Method: external merge Disk: 34,048kB
  • Buffers: shared hit=345 read=174,856, temp read=77,128 written=79,135
3. 206.145 7,660.878 ↑ 1.6 783,622 1

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

  • Buffers: shared hit=339 read=174,856, temp read=71,514 written=73,501
4. 419.729 7,454.733 ↑ 1.6 783,653 1

Sort (cost=896,430.38..899,493.43 rows=1,225,222 width=76) (actual time=7,172.166..7,454.733 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=339 read=174,856, temp read=71,514 written=73,501
5. 84.167 7,035.004 ↑ 1.6 783,653 1

Append (cost=633,015.63..663,647.18 rows=1,225,222 width=76) (actual time=6,250.567..7,035.004 rows=783,653 loops=1)

  • Buffers: shared hit=336 read=174,856, temp read=65,899 written=67,866
6. 294.514 6,950.780 ↑ 1.6 783,543 1

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

  • Buffers: shared hit=335 read=174,856, temp read=65,899 written=67,866
7. 1,224.097 6,656.266 ↑ 1.2 1,035,294 1

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

  • Sort Key: ci.geoname_id, acn.city_term, ci.country_code
  • Sort Method: external merge Disk: 44,336kB
  • Buffers: shared hit=335 read=174,856, temp read=65,899 written=67,866
8. 354.008 5,432.169 ↑ 1.2 1,035,294 1

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

  • Buffers: shared hit=335 read=174,856, temp read=57,516 written=59,456
9. 78.752 4,395.868 ↑ 1.0 807,348 1

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

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=331 read=174,856, temp read=57,516 written=59,456
10. 942.310 4,317.116 ↑ 1.2 269,116 3 / 3

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

  • Buffers: shared hit=331 read=174,856, temp read=57,516 written=59,456
11. 64.879 64.879 ↑ 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.123..64.879 rows=269,116 loops=3)

  • Buffers: shared hit=193 read=5,175
12. 1,706.399 3,309.927 ↑ 1.3 4,007,984 3 / 3

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

  • Buffers: shared read=169,681, temp written=53,788
13. 1,603.528 1,603.528 ↑ 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=238.55..1,603.528 rows=4,007,984 loops=3)

  • Buffers: shared read=169,681
14. 0.260 682.293 ↑ 1.0 383 1

Hash (cost=7.83..7.83 rows=383 width=3) (actual time=682.292..682.293 rows=383 loops=1)

  • Buffers: shared hit=4
15. 682.033 682.033 ↑ 1.0 383 1

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

  • Buffers: shared hit=4
16. 0.057 0.057 ↑ 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.041..0.057 rows=110 loops=1)

  • Buffers: shared hit=1
Planning time : 3.07 ms
Execution time : 11,114.665 ms