1

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
2
  • Please always provide your version of Postgres. And is this your actual use case? All arrays of same length? And retrieve all rows? Can there be NULL elements? Empty arrays? NULL arrays? And do you want to include a row for each of these cases in the result? Jan 25, 2016 at 0:34
  • Can you adapt your example to show how LIMIT will be applied? In combination with WHERE conditions or varying ORDER BY? Jan 26, 2016 at 2:04

2 Answers 2

2

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;
4
  • Does using a GiST index with custom operator class help? Jan 25, 2016 at 3:54
  • @TahaJahangir: I am tempted to say: "depends on the operator class", but I don't see how that could work at all. The default operator class certainly doesn't. Jan 25, 2016 at 4:13
  • You say "Postgres indexes can only hold a single index entry for a single table row." Does this mean that it is impossible to have a separate index for each element?
    – Vérace
    Jan 25, 2016 at 4:19
  • @Vérace: Each index would have one index entry per table row, so there is no conflict with my statement. Jan 26, 2016 at 2:00
1

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."

1
  • bar arrays are in variable lengths. Jan 25, 2016 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.