From: | Mark kirkwood <markir(at)slingshot(dot)co(dot)nz> |
---|---|
To: | Ryan Littrell <ryan(at)heliosinc(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: performance issue with distance function |
Date: | 2001-07-29 04:30:13 |
Message-ID: | 01072916301301.01095@spikey.slithery.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi Ryan,
There is a bit of a strange way around the distance overhead issue :
Create another table with structure like
(lat1,long1,zip1,lat2,long2,zip2,distance)
and precalculate the distance for each possibility. This means n*(n-1) rows
if you have n location rows. You would then include this table in your query
and use distance like you wanted to initially ( should work fast provided you
index it on lat1,long1,distance)
The calculation overhead of distance is then removed from your query ( at the
expense of some disk space ). The insert of each new location requires n
calculations of distance - you could perform this in the background I guess !
regards
Mark
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-07-29 20:02:32 | Re: Re: Restriction by grouping problem. |
Previous Message | Tom Lane | 2001-07-29 03:15:23 | Re: nullif BUG??? |