Why is this doing a seq scan?

From: "Ingram, Bryan" <BIngram(at)sixtyfootspider(dot)com>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: Why is this doing a seq scan?
Date: 2000-11-17 18:49:49
Message-ID: 01CCE949D2717845BA2E573DC081167E052FAE@BKMAIL.sfsinternal.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I'm trying to find the nearest locations to a certain point using 2 tables.
One contains the address and zipcodes of the locations and is about 2000
rows, the other contains zipcodes and lat, lon values and has about 1.4M
rows.

I've got indexes on the zip column of both tables and I just need to pull
the lat and lon out of the zips table for each zipcode that happens to be in
the ATMs table.

There are only about 2000 rows in ATMs, and since both the atms.zip and
zips.zip are indexed, I'm not sure why a seq scan is being performed.

The select is taking anywhere from 30secs to 1min. it's running on a linux
box w/2 pIII/700s and a raid ..so the machine shouldn't be slowing me down.
I think it's the seq scan but I can't seem to get rid of it.

=> explain select ( point(32.85, -94.55) <@> point(y.lat, y.lon) ) as
distance, x.zip, y.zip, y.lat, y.lon from atms x, zips y where x.zip = y.zip
order by 1 limit 3;
NOTICE: QUERY PLAN:

Sort (cost=39164156.66..39164156.66 rows=32338349 width=40)
-> Nested Loop (cost=0.00..30401394.25 rows=32338349 width=40)
-> Seq Scan on zips y (cost=0.00..29558.49 rows=1401749 width=28)
-> Index Scan using atms_zip on atms x (cost=0.00..21.38 rows=23
width=12)

Any idea on how to speed this up?

Thanks,
Bryan

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2000-11-17 19:07:52 Re: Requests for Development
Previous Message Ross J. Reedstrom 2000-11-17 17:30:29 Re: Requests for Development