從範圍數組中僅選擇重疊元素

我需要搜尋的表包含一個 numrange 值數組，如下所示：

CREATE TABLE data ( sensor varchar(25), ranges numrange[] );
INSERT INTO data VALUES
 ('sensor0','{"[872985609.0,873017999.0]","[873021600.0,873035999.0]","[873039600.0,873072070.0]"}'::numrange[])
, ('sensor1','{"[872929250.000000,872985609.000000]"}'::numrange[]);

ranges使用與指定範圍重疊的元素搜尋行很容易ANY：

SELECT * FROM data WHERE '[873021700,873021800]'::numrange && ANY (ranges)

我想要一個 SELECT 語句，它返回表中的欄位以及數組中與指定範圍重疊的元素，即*：*

SELECT sensor,[array of overlapping numranges] FROM data WHERE '[873021700,873021800]'::numrange && ANY (ranges)

導致：

sensor      ranges
sensor0     {"[873021600.0,873035999.0]"}

unnest()如果沒有子選擇中的數組，這可能嗎？unnest()如果不是，當數組可能非常大時，什麼是有效的方法？

這可以按需要工作：

SELECT d.sensor, r.overlapping_ranges
FROM   data d
JOIN   LATERAL (
  SELECT array_agg(range) AS overlapping_ranges
  FROM   unnest(d.ranges) range
  WHERE  range && '[873021700,873021800]'::numrange 
  ) r ON overlapping_ranges IS NOT NULL;

關於LATERAL：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

ranges對於大表，使用單獨的表（每行一個範圍）而不是范圍數組來規範化您的設計會更有效。您可以為此使用 GiST 索引：

• PostgreSQL：如何查詢具有自定義範圍的列？
• tsrange 上的 2 個 B-tree 索引或 1 個 GiST 索引——哪個表現更好？

---

大表解決方案

對於您在評論中提到的一個巨大的表（10 億行），我會考慮一個單獨的ranges表，針對大小和一個BRIN 索引進行了優化。

假設：

• 只讀（或大部分）數據。
• 最多 6 個小數位數 ( scale )，總共最多 18 個數字 ( precision )。按 1000000 縮放，這適合bigint無損失，儲存起來要便宜得多。見下文。
• Postgres 9.5 或更高版本。

Postgres 9.5 附帶的範圍類型的運算符類range_inclusion_ops是，它支持重疊運算符&&。

為了進一步優化磁碟空間，我只需保存兩個bigint數字（您的數值乘以 1000000）並使其成為功能性 BRIN 索引。基本上是這樣的：

CREATE TABLE sensors (
 sensor_id serial PRIMARY KEY
, sensor text NOT NULL);

CREATE TABLE ranges (
 sensor_id int NOT NULL REFERENCES sensors
, range_low bigint NOT NULL
, range_hi  bigint NOT NULL
);

INSERT INTO sensors (sensor) VALUES ('sensor1');

INSERT INTO ranges (sensor_id, range_low, range_hi) VALUES
  (1, 872985609.0 * 1000000, 873017999.0 * 1000000)  -- scaled
, (1, 872929250.000000 * 1000000, 872985609.000000 * 1000000);

CREATE INDEX ranges_brin_idx ON ranges USING BRIN (int8range(range_low, range_hi, '[]'));

查詢得到與之前相同的結果：

SELECT s.sensor, r.ranges
FROM  (
  SELECT sensor_id
       , array_agg(numrange(range_low * .000001, range_hi * .000001, '[]')) AS ranges
  FROM   ranges
  WHERE  int8range(range_low, range_hi, '[]')
      && '[873021700000000,873021800000000]'::int8range  -- scaled as well
  GROUP  BY sensor_id
  ) r
JOIN   sensors s  USING (sensor_id);

儲存大小bigint對比numrange

numrange精度為 15的 A在磁碟上佔用 32 個字節，因此每行 64 個字節（加上 int 列、元組標題和項目標識符）。

雖然兩bigint列（2 x 8 字節）相同，但總共有 52 個字節。使大約12 GB 的表更小。索引大小相同。

你自己看：

SELECT pg_column_size((1::bigint, '[873021700.123456,873021800.123456]'::numrange))
    , pg_column_size((1::bigint, 873021700123456::bigint, 873021700123456::bigint));

行大小詳解：

• 為讀取性能配置 PostgreSQL

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