From: Ingram, Bryan <BIngram@sixtyfootspider.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Why is this doing a seq scan?
Date: Fri, 17 Nov 2000 12:49:49 -0600
Message-ID: <01CCE949D2717845BA2E573DC081167E052FAE@BKMAIL.sfsinternal.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: BIngram@sixtyfootspider.com
Subject: Re: Why is this doing a seq scan?
In-Reply-To: <01CCE949D2717845BA2E573DC081167E052FAE@BKMAIL.sfsinternal.com>
References: <01CCE949D2717845BA2E573DC081167E052FAE@BKMAIL.sfsinternal.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox