My question is a follow-up to the question answered here:
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).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