Skip to content

Instantly share code, notes, and snippets.

@wjessop
Created October 17, 2009 10:36
Show Gist options
  • Save wjessop/212315 to your computer and use it in GitHub Desktop.
Save wjessop/212315 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 != '') 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=771.03..771.10 rows=30 width=363) (actual time=35.406..35.413 rows=30 loops=1)
-> Sort (cost=771.03..783.24 rows=4885 width=363) (actual time=35.404..35.405 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
-> Seq Scan on groups (cost=0.00..626.75 rows=4885 width=363) (actual time=0.038..26.495 rows=5126 loops=1)
Filter: ((NOT defunct) AND (lat IS NOT NULL) AND (lng IS NOT NULL) AND (full_address IS NOT NULL) AND ((full_address)::text <> ''::text))
Total runtime: 35.489 ms
(7 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment