Create an index over distinct values of a column in PostgreSQL

Question

I have a PostgreSQL table that looks like this:

CREATE TABLE items (
    name TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (name, value)
);

I frequently do a query to see what values are available:

SELECT DISTINCT value FROM items;

How do I create an index in PostgreSQL that the above query to not have to iterate over all of the items table?

Answer 1

Using a completely different query you can force PostgreSQL to use an index and get the equivalent of DISTINCT column..

https://wiki.postgresql.org/wiki/Loose_indexscan