Index selectivity

Let's take a look at the account_history table in the car web portal example, as follows:

CREATE TABLE account_history ( account_history_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, account_id INT NOT NULL REFERENCES account(account_id), search_key TEXT NOT NULL, search_date DATE NOT NULL, UNIQUE (account_id, search_key, search_date));

The unique constraint, UNIQUE (account_id, search_key, search_date), has two purposes. The first purpose is to define the validity constraint for inserting the search key into the table only once each day, even if the user searches for the key several times. The second purpose is to retrieve data quickly. Let's assume that we would like to show the last 10 searches for a user. The ...

Get Learning PostgreSQL 11 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.