Created
October 17, 2009 10:58
-
-
Save wjessop/212319 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
finder_development=> explain analyse SELECT *, (ACOS(least(1,COS(0.659296894483735)*COS(-2.13662186884198)*COS(RADIANS(groups.lat))*COS(RADIANS(groups.lng))+ COS(0.659296894483735)*SIN(-2.13662186884198)*COS(RADIANS(groups.lat))*SIN(RADIANS(groups.lng))+ SIN(0.659296894483735)*SIN(RADIANS(groups.lat))))*6376.77271) AS distance FROM "groups" WHERE (defunct = false AND lat is not null and lng is not null and full_address is not null and full_address != '' AND groups.lat>36.8764220242163 AND groups.lat<38.6734369757837 AND groups.lng>-123.556101941199 AND groups.lng<-121.282729058801 AND (ACOS(least(1,COS(0.659296894483735)*COS(-2.13662186884198)*COS(RADIANS(groups.lat))*COS(RADIANS(groups.lng))+ COS(0.659296894483735)*SIN(-2.13662186884198)*COS(RADIANS(groups.lat))*SIN(RADIANS(groups.lng))+ SIN(0.659296894483735)*SIN(RADIANS(groups.lat))))*6376.77271) <= 100) ORDER BY (ACOS(least(1,COS(0.659296894483735)*COS(-2.13662186884198)*COS(RADIANS(groups.lat))*COS(RADIANS(groups.lng))+ COS(0.659296894483735)*SIN(-2.13662186884198)*COS(RADIANS(groups.lat))*SIN(RADIANS(groups.lng))+ SIN(0.659296894483735)*SIN(RADIANS(groups.lat))))*6376.77271) ASC, num_members DESC LIMIT 30; | |
QUERY PLAN | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Limit (cost=29.19..29.20 rows=1 width=364) (actual time=1.153..1.159 rows=30 loops=1) | |
-> Sort (cost=29.19..29.20 rows=1 width=364) (actual time=1.152..1.155 rows=30 loops=1) | |
Sort Key: ((acos(LEAST(1::double precision, (((((-0.423756013910457)::double precision * cos(radians((lat)::double precision))) * cos(radians((lng)::double precision))) + (((-0.667232757379067)::double precision * cos(radians((lat)::double precision))) * sin(radians((lng)::double precision)))) + (0.612561252574016::double precision * sin(radians((lat)::double precision)))))) * 6376.77271::double precision)), num_members | |
Sort Method: top-N heapsort Memory: 40kB | |
-> Index Scan using index_groups_on_defunct_and_lat_and_lng_and_full_address on groups (cost=0.00..29.18 rows=1 width=364) (actual time=0.124..0.979 rows=67 loops=1) | |
Index Cond: ((defunct = false) AND (lat > 36.8764220242163) AND (lat < 38.6734369757837) AND (lng > (-123.556101941199)) AND (lng < (-121.282729058801))) | |
Filter: ((NOT defunct) AND (full_address IS NOT NULL) AND ((full_address)::text <> ''::text) AND ((acos(LEAST(1::double precision, (((((-0.423756013910457)::double precision * cos(radians((lat)::double precision))) * cos(radians((lng)::double precision))) + (((-0.667232757379067)::double precision * cos(radians((lat)::double precision))) * sin(radians((lng)::double precision)))) + (0.612561252574016::double precision * sin(radians((lat)::double precision)))))) * 6376.77271::double precision) <= 100::double precision)) | |
Total runtime: 1.235 ms | |
(8 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment