IP geolocating query too slow
<p>I've recently started using PostgreSQL at work &amp; I'm trying to optimize a query to geolocate users based on their IP address. I'm not entirely sure how to read the output from explain analyze. All tables have been vacuumed since last update, so I know that is not a cause for slowness.</p>

<p>I have the following tables:</p>

<p><strong>session_ipaddress:</strong><br>
stores visitors' IP address, and it has ~ 250,000 rows. The relevant columns &amp; indices:</p>

<pre><code>session_id VARCHAR PRIMARY KEY,
ip_address INET,
ip_int BIGINT

BTREE INDEX on ip_int
</code></pre>

<p><strong>ipblocks_201601:</strong><br>
MaxMind GeoLite2 City Blocks database obtained from <a href="http://dev.maxmind.com/geoip/geoip2/geolite2/" rel="nofollow">http://dev.maxmind.com/geoip/geoip2/geolite2/</a> with two additional columns <code>min_ip</code> &amp; <code>max_ip</code> that together hold the range of IP addresses in a CIDR block. The relevant columns and indices are:</p>

<pre><code>network CIDR PRIMARY KEY,
geoname_id INTEGER,
min_ip BIGINT,
max_ip BIGINT

BTREE INDEX ON geoname_id
BTREE INDEX ON min_ip
BTREE INDEX ON max_ip
</code></pre>

<p><strong>ipgeolookup_201601:</strong><br>
GeoLite2 Location Database for the <code>en</code> locale. The relevant columns &amp; indices:</p>

<pre><code>geoname_id INTEGER PRIMARY KEY,
country_name VARCHAR,
subdivision_1_name VARCHAR,
city_name VARCHAR

BTREE INDEX ON country_name
BTREE INDEX ON subdivision_1_name
BTREE INDEX ON city_name
</code></pre>

<p>Here's the query I am running, and it takes ~ 20s to complete.</p>

<pre><code>SELECT
  geo.country_name
, geo.subdivision_1_name region_name
, geo.city_name
, COUNT(s.session_id) location_unresolved
FROM session_ipaddress s
JOIN ipblocks_201601 ip ON ip.min_ip &lt;= s.ip_int AND ip.max_ip &gt;= s.ip_int
JOIN ipgeolookup_201601 geo ON geo.geoname_id = ip.geoname_id
WHERE geo.country_name = 'United States' OR geo.country_name = 'Canada'
GROUP BY 1, 2, 3;
</code></pre>

<p>Total runtime: 22192.814 ms &amp; this is the output from <code>EXPLAIN ANALYZE</code>: <a href="http://explain.depesz.com/s/DNcV" rel="nofollow">http://explain.depesz.com/s/DNcV</a></p>