Search⌘ K
Home
Courses
The Art of PostgreSQL

Indexing and Lookup

Explore how to implement indexing and nearest-neighbor lookups in PostgreSQL using GeoNames data. Understand index scans, lateral joins, and kNN searches to optimize queries on large geolocated datasets.

We'll cover the following...

The geoname table creation script contains the following index definition:

PostgreSQL
create index on geoname.geoname using gist(location);

Such an index is useful when searching for a specific location within our table, which contains about 11.5 million entries. PostgreSQL supports index scan-based lookups in several situations, including the kNN lookup, also known as the nearest-neighbor lookup.

In the Arrays chapter’s nonrelational data type example, we loaded a dataset of 200,000 Geolocalized tweets in the hashtag table. Here’s an extract of this table’s content:

─[ RECORD 1 ]────────────────────────────────────────────────
id       │ 720553458596757504
date     │ 2016-04-14 10:05:00+02
uname    │ Police Calls 32801
message  │ #DrugViolation at 335 N Magnolia
...