為任意 JSON 屬性查詢設計 Postgres 數據庫模式

在我的 Postgres 數據庫中，我有大量數據（一百萬行），架構如下所示：

create table test_tb (
 id int,
 tags jsonb
);

要對此數據集執行的一些查詢是：

select id, tags ->> 'tag_1' as tag_1
from test_tb
where tags ->> 'tag_1'= 'val_1'

select id, tags ->> 'tag_2' as tag_2
from test_tb

select id, tags ->> 'tag_1'
from test_tb
WHERE (tags ->> 'tag_1'='dv_0'
 AND tags ->> 'tag_3'='dv_15')
 AND (tags ->> 'tag_15'='dv_22' OR tags ->> 'tag_5'='dv_6')
  OR (tags ->> 'tag_12'='dv_9'); 

現在每個元組中的標籤是完全任意的，一個標籤可能只出現在一個元組中，而另一個標籤出現在數百個元組中，每個元組中的標籤數量約為 20 - 30。

我嘗試將標籤儲存在一jsonb列中並將 GIN 索引放在該列上，但它沒有優化我的查詢。請建議一些替代模式。

您必須重寫查詢，以便它們可以使用 GIN 索引。對於第一個查詢，這將是：

SELECT id, tags ->> 'tag_1' as tag_1
FROM test_tb
WHERE tags @> '{ "tag_1": "val_1" }';

對於第二個查詢，沒有索引可以提供幫助，因為沒有WHEREorORDER BY子句。

第三個查詢很棘手，因為它包含OR，但由於結果集包含主鍵，您可以重寫它以使用索引：

  SELECT id, tags ->> 'tag_1'
  FROM test_tb
  WHERE tags @> '{ "tag_1": "dv_0", "tag_3": "dv_15", "tag_15": "dv_22" }'
UNION
  SELECT id, tags ->> 'tag_1'
  FROM test_tb
  WHERE tags @> '{ "tag_1": "dv_0", "tag_3": "dv_15", "tag_5": "dv_6" }'
UNION
  SELECT id, tags ->> 'tag_1'
  FROM test_tb
  WHERE tags @> '{ "tag_12": "dv_9" }';

在這裡，UNION使用 代替OR。查詢變得更長，但每個子查詢都可以使用索引。

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