為複合類型數組中的欄位創建索引

我的問題是這裡回答的問題的後續：

https ://stackoverflow.com/a/15041094/994263

考慮一個具有復合類型數組列的表，PostgreSQL 是否可以對該列進行索引，以便能夠搜尋包含與某個任意謂詞匹配的數組條目的行。

這是一個開始問題的小提琴：https ://dbfiddle.uk/?rdbms=postgres_14&fiddle=cbed38d77f5fb7e2bc3d14605e74d464

我們有以下架構：

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

是否有一種機制可以對這樣的查詢進行索引查找：

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

這背後的基本原理可能是讓諸如多邊形之類的項目具有少量點（x，y），然後輕鬆查找哪些多邊形超出範圍。這是一種更類似於 NoSQL 的方法，如果它在不訴諸jsonb.

可以為複合類型數組中的欄位創建索引，但數組的適用索引類型是 GIN。並且GIN 不支持適用於您的查詢謂詞的運算符WHERE (co[*].r) BETWEEN 9.65 and 9.67。所以畢竟不可能 - 使用目前的 Postgres 14，可能還有未來的版本。

不過，這個問題只是學術上的，因為你一開始就不會這樣做。解決方案是適當的關係設計。

每當您發現自己創建了一個具有復合類型數組的表時，請停止並停止。由於機率接近確定，您應該創建一個單獨的表格，至少規範化您的設計。

然後一切變得相當簡單。可能看起來像這樣：

-- 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;

現在，索引很簡單：

CREATE INDEX tbl2_r_idx ON tbl2 (r, tbl2_id);

任何帶前導的 B 樹索引都可以r完成這項工作。較小的附加優化取決於完整的案例。

查詢類似於：

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

或者，如果您需要的不僅僅是不同的 ID：

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<>在這裡擺弄

任何一個查詢都可以使用非常快速的僅索引掃描和足夠真空的表，或者至少是索引掃描。

旁白

對於您的原始場景，您可以只使用內置類型point。它由兩個float8數量組成，與您的自定義行類型完全相同。使用下標引用第一個和第二個數字，例如：

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

看：

• 緯度和經度的數據類型是什麼？

您在原始測試案例中的查詢可以重寫為：

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

但是，閱讀您的基本原理，您可能會首先考慮PostGIS，或者至少考慮主線幾何類型。

引用自：https://dba.stackexchange.com/questions/307484