explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vogS : Slow geocoding with PostGIS

Settings
# exclusive inclusive rows x rows loops node
1. 38,959.834 363,608.219 ↓ 0.0 0 1

Update on addresses_to_geocode (cost=1.30..8,390.04 rows=1,000 width=272) (actual time=363,608.219..363,608.219 rows=0 loops=1)

2. 19.586 324,648.385 ↑ 1.0 1,000 1

Merge Left Join (cost=1.30..8,390.04 rows=1,000 width=272) (actual time=110.934..324,648.385 rows=1,000 loops=1)

  • Merge Cond: (a.patientid = g.patientid)
3. 2.462 34.241 ↑ 1.0 1,000 1

Nested Loop (cost=0.86..8,336.82 rows=1,000 width=184) (actual time=10.676..34.241 rows=1,000 loops=1)

4. 1.301 18.779 ↑ 1.0 1,000 1

Subquery Scan on a (cost=0.43..54.32 rows=1,000 width=32) (actual time=10.664..18.779 rows=1,000 loops=1)

5. 0.457 17.478 ↑ 1.0 1,000 1

Limit (cost=0.43..44.32 rows=1,000 width=4) (actual time=10.658..17.478 rows=1,000 loops=1)

6. 17.021 17.021 ↑ 4,449.8 1,000 1

Index Scan using "addresses_to_geocode_PatientId_idx" on addresses_to_geocode addresses_to_geocode_1 (cost=0.43..195,279.22 rows=4,449,758 width=4) (actual time=10.657..17.021 rows=1,000 loops=1)

  • Filter: (rating IS NULL)
  • Rows Removed by Filter: 24110
7. 13.000 13.000 ↑ 1.0 1 1,000

Index Scan using "addresses_to_geocode_PatientId_idx" on addresses_to_geocode (cost=0.43..8.27 rows=1 width=152) (actual time=0.010..0.013 rows=1 loops=1,000)

  • Index Cond: ("PatientId" = a.patientid)
8. 1.123 324,594.558 ↑ 1.1 943 1

Materialize (cost=0.43..18.22 rows=1,000 width=96) (actual time=100.233..324,594.558 rows=943 loops=1)

9. 1.832 324,593.435 ↑ 1.1 943 1

Subquery Scan on g (cost=0.43..15.72 rows=1,000 width=96) (actual time=100.230..324,593.435 rows=943 loops=1)

10. 0.457 324,591.603 ↑ 1.1 943 1

Limit (cost=0.43..5.72 rows=1,000 width=42) (actual time=100.225..324,591.603 rows=943 loops=1)

11. 324,591.146 324,591.146 ↑ 4,718,725.3 943 1

Index Scan using "addresses_to_geocode_PatientId_idx" on addresses_to_geocode ag (cost=0.43..23,534,259.93 rows=4,449,758,000 width=42) (actual time=100.225..324,591.146 rows=943 loops=1)

  • Filter: (rating IS NULL)
  • Rows Removed by Filter: 24110