# IP address lookups in BigQuery: a million in 3 seconds

BigQuery refused a BETWEEN join of 100,000 IP addresses to ranges. A join key at four levels looked up a million in 3.1 seconds.

We matched random IPv4 addresses to [VPN IP](https://internetdata.io/databases/vpn-ip)'s 12,767,039 ranges in BigQuery on October 16, 2026. The join most people write first, `ip BETWEEN start AND end`, took 11.2 seconds and 5.7 hours of slot time for 10,000 addresses, and BigQuery refused it at 100,000. Our table function, which joins on a key before it tests the range, looked up a million addresses in 3.1 seconds and billed 367 MB, about $0.002.

In 2013 someone asked on Stack Overflow [how to make a BigQuery GeoIP query faster](https://stackoverflow.com/questions/19618105/how-to-improve-performance-of-geoip-query-in-bigquery). A join could only test `=` then, so their query paired every address with every range on a constant and filtered afterwards. A join takes `BETWEEN` now, and BigQuery still runs it that way.

## Four ways, measured

| Lookup | Addresses | Wall time | Billed | Slot time |
|---|---|---|---|---|
| The range join below | 10,000 | 11.2 s | 154 MB | 20,382 s |
| The same range join | 100,000 | refused at 180.9 s | nothing | 258,120 s |
| `lookup_vpn_ip_v1`, the table function | 1,000,000 | 3.1 s | 367 MB | 34 s |
| `vpn_ip_v1(ip)`, the scalar function | 1,000,000 | 6.8 s | 932 MB | 90 s |

Here's the range join as it ran at 10,000 addresses, in our own project with VPN IP's dataset as the default. `ips` draws random IPv4 addresses with a first octet from 1 to 222, and the `LENGTH` test keeps them off IPv6 ranges, whose bytes compare with an IPv4 address's too:

```sql
WITH ips AS (
  SELECT FORMAT('%d.%d.%d.%d',
      CAST(1 + FLOOR(RAND() * 222) AS INT64),
      CAST(FLOOR(RAND() * 256) AS INT64),
      CAST(FLOOR(RAND() * 256) AS INT64),
      CAST(FLOOR(RAND() * 256) AS INT64)) AS ip
  FROM UNNEST(GENERATE_ARRAY(1, 10000))
)
SELECT COUNT(*) AS matches
FROM ips
JOIN vpn_ip_v1 AS v
  ON NET.SAFE_IP_FROM_STRING(ips.ip) BETWEEN v.start_ip_bytes AND v.end_ip_bytes
  AND LENGTH(v.start_ip_bytes) = 4
```

It found 44 matches. Its query plan has the join as `CROSS EACH WITH EACH` and the `BETWEEN` as a filter after it: each address paired with each of VPN IP's 12,762,048 IPv4 ranges, 127.6 billion pairs. At 100,000 addresses BigQuery stopped it after three minutes:

> Query exceeded resource limits. This query used 217193 CPU seconds but would charge only 147M Analysis bytes. This exceeds the ratio supported by the on-demand pricing model. Please consider moving this workload to a capacity-based pricing model, which does not have this limit. 217193 CPU seconds were used, and this query must use less than 37600 CPU seconds.

The limit counts CPU seconds, a different measure from the slot time in the table. [On-demand pricing](https://cloud.google.com/bigquery/pricing) bills the bytes a query reads, and caps the CPU it may spend per byte. A range join reads two columns, 154 MB here, and spends its CPU comparing them. The refused query billed nothing.

## A key BigQuery can hash

A join on `=` runs as a hash join, which pairs an address only with the rows that share its key. Give each range a key and each address the same one, and `BETWEEN` only has to sort out the rows under it. Google Cloud's [2019 post](https://cloud.google.com/blog/products/data-analytics/geolocation-with-bigquery-de-identify-76-million-ip-addresses-in-20-seconds/) masks each address at every prefix length from /9 to /32 and joins on the network, which fits a table where every row is one CIDR network. IPinfo's [2023 guide](https://community.ipinfo.io/t/querying-ipinfo-ip-databases-inside-bigquery/4579) truncates each address to its /16 for IPv4 and its /34 for IPv6, then filters with `BETWEEN`.

With a single key length, a range wider than the key is stored once for every key it spans: [Bogon IP](https://internetdata.io/databases/bogon-ip)'s 16 IPv4 ranges become 9,050 rows keyed at /16 and 2,315,270 at /24, and its 64 IPv6 ranges 3,816,895,859 rows at /32. Ranges narrower than the key share one. On October 16, every VPN IP range in IPv4 fit inside a /16, but a /16 key held 361 of them on average and 45,138 in the fullest, and an address there was tested against all 45,138.

Our tables file each range once, in a `join_key` column: the network of the finest of /24, /16, /8 and /0 that holds the whole range, or of /64, /48, /32, /16 and /0 for IPv6. A lookup tries an address's network at each level, four keys for IPv4 and five for IPv6. VPN IP's keys held 5.6 ranges on average and 249 in the fullest on October 16, and Bogon IP stays at 80 rows.

![Diagram of a lookup of 10.1.2.3 in Bogon IP as built on October 16, 2026: of its four keys, 10.1.2.0/24 and 10.1.0.0/16 have no ranges filed, 10.0.0.0/8 holds the private-use range 10.0.0.0 to 10.255.255.255, which contains the address, and 0.0.0.0/0 holds multicast and reserved space, which don't.](/blog/media/bigquery-ip-lookup-keys.2x.png)

To key a range table of your own, take the finest level at which both ends share a network. This recomputes the keys of Bogon IP, one of the free tables below, from its ranges, and matches ours on all 80 rows:

```sql
SELECT start_ip, end_ip, join_key,
  FORMAT('%s/%d', NET.IP_TO_STRING(NET.IP_TRUNC(s, lvl)), lvl) AS my_key
FROM (
  SELECT *, (
      SELECT MAX(l)
      FROM UNNEST(IF(LENGTH(s) = 4, [24, 16, 8, 0], [64, 48, 32, 16, 0])) AS l
      WHERE NET.IP_TRUNC(s, l) = NET.IP_TRUNC(e, l)) AS lvl
  FROM (
    SELECT *, start_ip_bytes AS s, end_ip_bytes AS e
    FROM internetdata_open.bogon_ip_v1
  )
)
```

A lookup then joins each address's candidate keys and keeps the ranges that hold it:

```sql
SELECT a.ip, r.start_ip, r.end_ip, r.name
FROM (
  SELECT ip, NET.SAFE_IP_FROM_STRING(ip) AS b
  FROM UNNEST(['10.1.2.3', '8.8.8.8', '::1']) AS ip
) AS a
CROSS JOIN UNNEST(IF(LENGTH(a.b) = 4, [24, 16, 8, 0], [64, 48, 32, 16, 0])) AS l
JOIN internetdata_open.bogon_ip_v1 AS r
  ON r.join_key = FORMAT('%s/%d', NET.IP_TO_STRING(NET.IP_TRUNC(a.b, l)), l)
  AND a.b BETWEEN r.start_ip_bytes AND r.end_ip_bytes
```

It returns the private-use range for `10.1.2.3`, nothing for `8.8.8.8`, and three nested ranges for `::1`. Its plan joins on the key with an `INNER HASH JOIN`, as the table function's does.

## With the InternetData Open listing

Our [BigQuery listings](https://docs.internetdata.io/integrations/bigquery) come keyed this way, with a lookup function for each table keyed by address. `InternetData Open` is free under [CC BY-SA 4.0](https://creativecommons.org/licenses/by-sa/4.0/) and holds eleven databases: [ASN](https://internetdata.io/databases/asn), [IP ASN](https://internetdata.io/databases/ip-asn), [IP Country](https://internetdata.io/databases/ip-country), [IP Timezone](https://internetdata.io/databases/ip-timezone), [IP Country Flag](https://internetdata.io/databases/ip-country-flag), [IP Country Currency](https://internetdata.io/databases/ip-country-currency), [Anycast IP](https://internetdata.io/databases/anycast-ip), [Bogon IP](https://internetdata.io/databases/bogon-ip), [Bogon ASN](https://internetdata.io/databases/bogon-asn), [Tor IP](https://internetdata.io/databases/tor-ip) and [Relay IP](https://internetdata.io/databases/relay-ip), each described in [Free IP databases](https://internetdata.io/blog/free-ip-databases). In the BigQuery console, open Sharing, search for InternetData and subscribe to `InternetData Open`, or to `InternetData Open EU` for data in the EU, since BigQuery doesn't join across regions. Name the dataset it adds `internetdata_open` and this post's queries on the Open tables run as printed. The listing shows us the email address of each account that queries it.

Each table keyed by address has a function of its name, which returns the row covering an address as a `STRUCT`, the narrowest where several do:

```sql
SELECT ip,
    internetdata_open.tor_ip_v1(ip).confidence AS tor,
    internetdata_open.bogon_ip_v1(ip).name AS bogon
FROM UNNEST(['185.220.101.1', '10.1.2.3', '::ffff:10.1.2.3', '::1',
    'not an address']) AS ip
```

| ip | tor | bogon |
|---|---|---|
| 185.220.101.1 | high | |
| 10.1.2.3 | | Private-use networks |
| ::ffff:10.1.2.3 | | Private-use networks |
| ::1 | | Node-scope unicast loopback address |
| not an address | | |

A mapped address is looked up as the IPv4 it carries, and a value that isn't an address is a miss. For every covering row, the table function with a `lookup_` prefix takes a table with an `ip` column and returns a row per match:

```sql
SELECT ip, name
FROM internetdata_open.lookup_bogon_ip_v1((
    SELECT ip FROM UNNEST(['::1', '::ffff:10.1.2.3']) AS ip))
```

| ip | name |
|---|---|
| ::1 | IPv4-compatible addresses |
| ::1 | Node-scope unicast loopback address |
| ::1 | Unallocated: outside global unicast (2000::/3) |
| ::ffff:10.1.2.3 | Private-use networks |

On a table of your own, select its addresses in place of the list, as the [BigQuery docs page](https://docs.internetdata.io/integrations/bigquery) does with `mydataset.logins`. Each column is described on its database's docs page: [Tor IP's](https://docs.internetdata.io/database/tor-ip) `confidence`, [Bogon IP's](https://docs.internetdata.io/database/bogon-ip) `name`.

## What it costs

Your queries run and bill in your own project, and the tables cost you nothing to store. Each is rebuilt the day its database changes, daily for most, and a subscription always reads the current one, so it never ages the way a downloaded copy does ([what an old copy misses](https://internetdata.io/blog/ip-database-update-frequency)).

A function reads every row of its table, however few addresses you pass it. The scalar reads every column: VPN IP's billed 932 MB for a million addresses, and a dry run of one address estimated the same that day. The table function reads the key, the range and the columns you select: 367 MB billed to count matches, and 377 MB estimated with `provider`. At $6.25 per TiB, a million lookups cost $0.002 through the table function and $0.005 through the scalar, and BigQuery's first TiB each month is free. Tor IP and Bogon IP are small enough that a query bills BigQuery's 10 MB minimum for each.

Most of our other databases are listings of their own, under the license you hold for each ([the list](https://docs.internetdata.io/integrations/bigquery)). [Talk to us](https://internetdata.io/contact?purpose=licensing&subject=Databases+in+BigQuery) to add one in BigQuery.

## How we measured

Everything ran on October 16, 2026, in BigQuery's US multi-region at on-demand rates, against VPN IP as built that day: 12,767,039 ranges, 12,762,048 of them IPv4, in a 932 MB table ([its columns](https://docs.internetdata.io/database/vpn-ip/vpn-ip)). We ran in the project our tables are published from, with VPN IP's dataset as the default, so `vpn_ip_v1` needs no prefix; in your project it sits in the dataset you named when you subscribed. The four queries on the Open tables ran again on October 18 from a subscription in another project, as an account with no access to the project our tables are published from, and returned the rows printed here. Each row of the first table is one run of one query, none served from cache. The function rows put a million addresses from the same `ips` into `lookup_vpn_ip_v1`, counting the 4,318 rows it returned, and into `vpn_ip_v1(ip)`, counting the 4,239 addresses it didn't return NULL for, each run drawing its own addresses. Wall time is the job's start to its end, and slot time the job's total. A key's size counts the ranges filed under it: under a single /16 key, a range in every /16 it spans.

To license VPN IP or another database, see [pricing](https://internetdata.io/pricing) or [talk to us](https://internetdata.io/contact).

