GeoIP index

From PostgreSQL wiki

Revision as of 14:02, 18 May 2012 by Boshomi (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Library Snippets

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

See also

Personal tools