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?

Urkle · Accepted Answer · 2017-08-23 15:19:53Z

13

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

answered Aug 23, 2017 at 15:19

Urkle

1,68815 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1