Postgresql: Sortable index on array values

Question

Suppose the following table:

CREATE TABLE foo (id serial, category int, bar int[]);
INSERT INTO foo VALUES (1, 1, '{4,10,20}');
INSERT INTO foo VALUES (2, 1, '{1,8,9}');

We want to query sorted individual values from bar column. This can be achieved with this query:

SELECT unnest(bar) AS u, id FROM foo ORDER BY u;                                                                                                                 
 u  | id 
----+----
  1 |  2
  4 |  1
  8 |  2
  9 |  2
 10 |  1
 20 |  1

How we can build an index for this query?

UPDATE:

• Version of postgres is not important, lets say we want to implement with latest version (9.5)
• bar column is variable-length, different rows may have different number of elements, (but no empty array).
• Elements are not null.
• We want an index to support efficient queries with LIMIT (so not retrieving all rows in queries, see blow)
• This is a simplified version of an actual (in design) use-case.

UPDATE: Example with LIMIT clause

SELECT unnest(bar) AS u, id FROM foo WHERE category = 1 ORDER BY u LIMIT 3;                                                                                                                 
 u  | id 
----+----
  1 |  2
  4 |  1
  8 |  2

Answer 1

If your use case is to retrieve all array elements of all rows, an index is not going to help with that. Postgres indexes can only hold a single index entry for a single table row. And a GIN index would not support the sort.

The best option I see would be a MATERIALIZED VIEW - which needs a refresh after any write operations on the underlying table that might influence the result. So probably only useful for read-only (or mostly) tables.

CREATE MATERIALIZED VIEW foo_elements AS
SELECT unnest(bar) AS u, id
FROM   foo
ORDER  BY u;

Answer 2

An answer to your query can be found here.

CREATE TABLE test (foo int[]);
INSERT INTO test VALUES ('{1,2,3}');
INSERT INTO test VALUES ('{4,5,6}');
CREATE INDEX test_index on test ((foo[1]));

Must be version >= 9.2.1. You have to create a separate index for each element. But as your friend Herr Brandstetter points out in a comment, it's not really of much use because if you

"have a fixed number of array elements, you'd rather use individual columns for each element (and plain btree indices) instead of building a more expensive expression index for each array item. Storage of individual columns is much cheaper without array overhead, too."