GeoIP index
From PostgreSQL wiki
Spatial index on GeoIP data to speed up fetches
Works with PostgreSQL
Any version
Written in
SQL
Depends on
Nothing
If your are using PostgreSQL + ip2location (or MaxMind), creating the following spatial index on the ip2location table results in a huge performance increase.
Create a spatial index on the ip_from/ip_to attributes:
-- Taken from http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Fast_interval_.28of_time_or_ip_addresses.29_searching_with_spatial_indexes
CREATE INDEX ip2location_range_gist ON ip2location USING gist ((box(point(ip_from,ip_from),point(ip_to,ip_to))) box_ops); analyze verbose ip2location; EXPLAIN analyze SELECT * FROM ip2location WHERE box(point(ip_from,ip_from),point(ip_to,ip_to)) @> box(point (3512069689,3512069689), point(3512069689,3512069689));
Query version where the IP needs to be converted to bigint:
EXPLAIN analyze SELECT * FROM ip2location WHERE box(point(ip_from,ip_from),point(ip_to,ip_to)) @> box( point(inet_to_bigint('209.85.238.57'),inet_to_bigint('209.85.238.57')), point(inet_to_bigint('209.85.238.57'),inet_to_bigint('209.85.238.57')) );
The inet_to_bigint functions come from that same page:
-- from http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Bigint_to_ip DROP FUNCTION IF EXISTS bigint_to_inet(BIGINT); CREATE FUNCTION bigint_to_inet(BIGINT) RETURNS inet AS $$ SELECT (($1>>24&255)||'.'||($1>>16&255)||'.'||($1>>8&255)||'.'||($1>>0&255))::inet $$ LANGUAGE SQL strict immutable; GRANT EXECUTE ON FUNCTION bigint_to_inet(BIGINT) TO public; DROP FUNCTION IF EXISTS inet_to_bigint(inet); CREATE OR REPLACE FUNCTION inet_to_bigint(inet) RETURNS BIGINT AS $$ SELECT $1 - inet '0.0.0.0' $$ LANGUAGE SQL strict immutable; GRANT EXECUTE ON FUNCTION inet_to_bigint(inet) TO public;
added by bricklen
