From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ingram, Bryan <BIngram@sixtyfootspider.com>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Why is this doing a seq scan?
Date: Fri, 17 Nov 2000 17:07:57 -0500
Message-ID: <14217.974498877@sss.pgh.pa.us> (raw)
In-Reply-To: <14140.974497193@sss.pgh.pa.us>
References: <01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com>
<14140.974497193@sss.pgh.pa.us>
I said:
> Ah, there's your problem --- the planner is not very smart about
> optimizing cross-datatype comparisons. Make these columns both text,
> or both varchar, and I'll bet you get a more intelligent plan.
After a little further thought, I realize that the planner may be
handicapped by not realizing it can do a merge or hash join across
datatypes, but even without that problem, this is not going to be
a fast query. What you've got is
select ... from atms x, zips y where x.zip = y.zip
order by 1 limit 3;
and there just isn't any way to process this without forming the
full join product --- ie, the thing will sit there and form a join
tuple for *every* valid combination of ATM and ZIP in your database,
and then compute the distance to the target point for every one of
those ATMs, and then sort that result, and finally give you only
the top three rows. A smarter kind of join isn't going to help
all that much; to make this fast, you need to be filtering
using the really selective condition (distance to the target point)
*before* you do the join.
If you are sufficiently interested in the speed of this query to want to
maintain a specialized index for it, I'd suggest looking at an r-tree
index on the location data, and then using a WHERE condition on the
r-tree index to prefilter the rows before you join. r-trees only work
on boxes and polygons AFAICT --- what would work nicely is to store a
"box" of very small dimensions surrounding the location of each ATM,
index that column, and then use a WHERE test for overlap between that
box column and a box surrounding the target point out to as far as you
think is likely to be interesting. This gives you back a fairly small
number of candidate ATMs for which you compute the exact distance to
the target, sort, and limit. Not sure that you need to join to zips
at all if you do it this way.
regards, tom lane
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: tgl@sss.pgh.pa.us, BIngram@sixtyfootspider.com
Subject: Re: Why is this doing a seq scan?
In-Reply-To: <14217.974498877@sss.pgh.pa.us>
References: <01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com> <14140.974497193@sss.pgh.pa.us> <14217.974498877@sss.pgh.pa.us>
* 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