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
LIMIT
will be applied? In combination withWHERE
conditions or varyingORDER BY
?