Indexing and Lookup
Learn about indexing and lookups in PostgreSQL.
We'll cover the following...
The geoname table creation script contains the following index definition:
Press + to interact
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