json - 调整 Postgres 全文搜索

Question

表格是产品:

             Table "public.product"
     Column      |           Type           |
-----------------+--------------------------+
 id              | uuid                     |
 attributes      | jsonb                    |

请注意，attributes 是一个 jsonb 字段。目前我有 ~5k 行，我正在这样查询:

select id, to_tsvector(attributes::text) @@ to_tsquery('22222') from product;

此查询已经需要几秒钟才能完成，我想知道我是否可以做些什么来缩短这段时间，即索引或改进查询？

开始这个查询返回:

                  id                  | found 
--------------------------------------+-------
 a8230602-ff3f-4414-affc-3594abcfa617 | f
 da0c70d5-5108-42ea-941d-123589129feb | f
 24ac417a-466c-465c-b346-4fad7a9ad3d8 | f
 4bee6122-c5d7-4e0c-840e-e04c28888a9a | f
 ce5fe539-bcb2-4cec-9012-b2501df7012e | f

这是不可取的，有没有办法只返回匹配的行？

Answer 1

您需要将条件移动到WHERE 子句:

SELECT *
FROM   product
WHERE  to_tsvector('english', attributes::text) @@ to_tsquery('22222');

并在表达式上创建全文索引:

CREATE INDEX textsearch_idx ON product
USING GIN (to_tsvector('english', attributes::text));

索引表达式和查询中的表达式必须匹配。

Details in the manual.

或者您可能能够使用 jsonb GIN 索引:

• What's the proper index for querying structures in arrays in Postgres jsonb?

但如果您想同时搜索键 和 值，那将行不通。

开始时使用规范化的表格布局可能会更好......