Skip to content

Instantly share code, notes, and snippets.

@wjessop
Created October 17, 2009 10:58
Show Gist options
  • Save wjessop/212319 to your computer and use it in GitHub Desktop.
Save wjessop/212319 to your computer and use it in GitHub Desktop.
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