1

My question is a follow-up to the question answered here:
https://stackoverflow.com/a/15041094/994263

Considering a table with an array column of a composite type, is it possible with PostgreSQL to index the column to be able to search for rows containing an array entry matching some arbitrary predicate.

Here is a fiddle to start off the problem: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=cbed38d77f5fb7e2bc3d14605e74d464

We have the following schema:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TABLE tbl2 (tbl2_id serial, co complex[]);

INSERT INTO tbl2(co)
select array_agg((random()* 100, random()*100)::complex)
from generate_series(1, 50000) i
group by i % 10000;

-- how to create an index on co[*].r basically?
CREATE INDEX tbl2_co1_idx ON tbl2 (((co[1]).r)); -- note the parentheses! 
-- * this is only a single array entry's r values

Is there a mechanism to do an indexed lookup for queries such as this:

SELECT * FROM
   (SELECT *,
           generate_subscripts(co, 1) AS s
    FROM tbl2) AS foo
WHERE (co[s].r) BETWEEN 9.65 and 9.67;

The rationale behind this could be to have items such as polygons for example with a small number of points (x,y) and to then lookup easily which polygons are out of bounds. It is a more NoSQL-like approach, which would be great if it is doable without resorting to jsonb.

3
  • Your fiddle uses Postgres 9.6. Is that your version? Consider dbfiddle.uk/?rdbms=postgres_14 for current Postgres versions. Commented Feb 14, 2022 at 20:07
  • I am actually on 13 at the moment, but I can use the latest if that can solve the question easier. I will update accordingly
    – Climax
    Commented Feb 14, 2022 at 20:17
  • I updated and clarified my old answer a bit. Seems like it fueled unrealistic expectations. Don't overlook its date of origin 9 years ago! Commented Feb 14, 2022 at 23:33

2 Answers 2

4

It is possible to create an index for fields within an array of composite type, but the applicable index type for arrays is GIN. And GIN does not support an operator that would work for your query predicate WHERE (co[*].r) BETWEEN 9.65 and 9.67. So not possible after all - with current Postgres 14, and probably future versions as well.

The problem is merely academic, though, because you wouldn't do that to begin with. The solution is a proper relational design.

Whenever you find yourself creating a table with an array of composite type, cease and desist. With a probability bordering on certainty, you should create a separate table instead, normalizing your design at least that much.

Then everything becomes rather simple. Could look like this:

-- parent table optional, not needed for demo
CREATE TABLE tbl2_parent (tbl2_id serial PRIMARY KEY);  -- more attributes?

-- random test data
INSERT INTO tbl2_parent
SELECT FROM generate_series(1, 1000) t;

-- main table
CREATE TABLE tbl2(
  tbl2_id int NOT NULL  -- REFERENCES tbl2_parent
, idx     int NOT NULL
, l       float8 NOT NULL
, r       float8 NOT NULL
, PRIMARY KEY (tbl2_id, idx)
);

-- random test data
INSERT INTO tbl2 (tbl2_id, idx, l, r)
SELECT t
     , i
     , random() * 100
     , random() * 100
FROM   generate_series(1, 1000) t, generate_series(1, 5) i
ORDER  BY 1, 2;

Now, the index is simple:

CREATE INDEX tbl2_r_idx ON tbl2 (r, tbl2_id);

Any B-tree index with leading r does the job. Minor additional optimizations depend on the complete use case.

And the query is something like:

SELECT DISTINCT tbl2_id
FROM   tbl2
WHERE  r BETWEEN 9.65 and 9.67;

Or, if you need more than distinct IDs:

EXPLAIN ANALYZE
SELECT *
FROM   tbl2_parent p
WHERE  EXISTS (
   SELECT FROM tbl2 t
   WHERE  t.r BETWEEN 9.65 AND 9.67
   AND    t.tbl2_id = p.tbl2_id
   );

db<>fiddle here

Either query can use very fast index-only scans with tables that are vacuumed enough, or at least index scans.

Asides

For your original scenario, you could just use the built-in type point. It consists of two float8 quantities, exactly like your custom row type. Reference first and second number with a subscript like:

SELECT ('(7,8)'::point)[0];  -- 7

See:

Your query in your original test case can be rewritten as:

SELECT *
FROM   tbl2 t
WHERE  EXISTS (
   SELECT FROM unnest(t.co) elem
   WHERE (elem.r) BETWEEN 9.65 and 9.67
   );

Reading your rationale, though, you might consider PostGIS first, or at least mainline geometric types.

8
  • thanks for the clear and thorough answer. It is as you say an academic problem, as I am studying the possibility of having a NoSQL (denormalised) paradigm in the relational world. With PostgreSQL being object relational it would seem appropriate to make use of arrays for simple one-to-few relationships, especially of the composition relationship kind. The specific polygon example is also purely academic, and was unfortunately the first example I could think of. A better example would have been telephone numbers. They are a composition relation to a user for example.
    – Climax
    Commented Feb 15, 2022 at 18:20
  • Disregarding composite user types, the jsonb column would have supported such a query and index?
    – Climax
    Commented Feb 15, 2022 at 18:21
  • 1
    @Climax: Yes, jsonb is more versatile for this kind of task (but also typically occupies more storage). Since Postgres 12 you can do "range searches" on nested values with the SQL/JSON path language and get index support for that. Example: stackoverflow.com/a/69731065/939860 Before that, there was no general way to support range searches for nested values. See: dba.stackexchange.com/a/202729/3684 A normalized design as demonstrated is typically superior in any case. It all depends ... Commented Feb 15, 2022 at 21:22
  • 1
    @Climax: As proof of concept: dbfiddle.uk/… Not useful for your case, as laid out above. Commented Mar 4, 2022 at 23:19
  • 1
    Basically, yes. If you are not actually using BETWEEN, then it's a different question. The elephant in the room is still the same: don't create a table column as array of composite type if you don't have to. Commented Mar 6, 2022 at 22:41
1

Such a gold conversation. I was riding a similar boat a few days back comparing normalized vs flexible data modeling in Postgres.

With so many NoSQL databases in the market, now flexible data modeling is in trend and general expectation is that joins makes queries slower and we can not create composite indexes on multiple tables in a normalized form.

I am still looking if there could be some development opportunities in Postgres to make such schema design efficient:

  1. Looks like currently there is no inbuilt function in Postgres which extracts data from nth position from every array from a two dimensional array. I think that's the reason Erwin created a custom function to get an array list from a tuple and then we could create an Index on top of that returning array.

  2. While using the Gin Index on the Integer array there is no range scan support. I am not very well versed with GIN index tree structure but I thought inside it creates a sorted tree on individual elements of the array, then why not it works with a range scan like a simple BTree Index . Is it due to the fact that someone did not bother about creating a new operator/operator class for such use cases or there are some architectural limitations.

Also while comparing a value we need to pass the value as an array. This reduces query readability as we could not pass on a value directly. Probably a new operator could help here.

select count(*) from TAB where arr = '{4}';
select count(*) from TAB where arr = 4;   → This does not work.
  1. Another point which was not touched on in this post is the vacuuming aspect of such denormalized data modeling. Here any single value change in a big array means we need to rewrite the entire row. I thought if Postgres could write just that affected chunk instead of all the chunks. I understand this is complex and we need to make sure MVCC rules are intact on toast row. But only this behavior is enough to deviate from this flexible model to a compact normalized data modeling.
1
  • It is an interesting conversation. On a TPC-C benchmark, I got 15-30% more TpmCs by including order lines in the order table as a column. That is a scenario where the column doesn't change a lot, so the vacuum aspect didn't hit.
    – Climax
    Commented Aug 12, 2022 at 13:40

Your Answer

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

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