我们正在PostgreSQL 12/13上试用JSONB，看看它是否比一堆扩展表(我想是EAV(更好地替代可定制的扩展属性。到目前为止，我对结果印象深刻，尽管使用GIN索引比最初看起来更棘手。
实验台很简单：
create TABLE jtest (
id SERIAL PRIMARY KEY,
text text,
ext jsonb
);
CREATE INDEX jtest_ext_gin_idx ON jtest USING gin (ext);
我正在插入一些不同的数据与(更大版本的(这个可怕的块(引用仅用于db-fiddle(：
DO 'BEGIN
FOR r IN 1..100000 LOOP
IF r % 10 <= 3 THEN
-- some entries have no extension
INSERT INTO jtest (text, ext) VALUES (''json-'' || LPAD(r::text, 10, ''0''), NULL);
ELSEIF r % 10 = 7 THEN
-- let''s add some numbers and wannabe "dates"
INSERT INTO jtest (text, ext)
VALUES (''json-'' || LPAD(r::text, 10, ''0''), (''{'' ||
''"hired": "'' || current_date - width_bucket(random(), 0, 1, 1000) || ''",'' ||
''"rating": '' || width_bucket(random(), 0, 1, 10) || ''}'')::jsonb);
ELSE
INSERT INTO jtest (text, ext)
VALUES (''json-'' || LPAD(r::text, 10, ''0''), (''{"email": "user'' || r || ''@mycompany.com", "other-key-'' || r || ''": "other-value-'' || r || ''"}'')::jsonb);
END IF;
END LOOP;
END';
各种精确值匹配操作都很容易，GIN对这些操作非常有效。但我们也需要<和LIKE，但现在我们只关注比较。
示例查询是：
select * from jtest
where ext->>'hired' >= '2020-06-01' -- not using function index on its own
但如果我加上语义上无用的AND，索引就会生效：
select * from jtest
where ext->>'hired' >= '2020-06-01'
and ext?'hired';
这是一个小提琴的例子。
问题1：在我们的应用程序中实现一个查询解释器使其工作是没有问题的，但这是预期的行为吗？难道PG不知道当使用>=时，左侧确实不是空的吗？
我还在(ext->>'hired')-fiddle上实验了函数索引：
CREATE INDEX jtest_ext_hired1_idx ON jtest ((ext->>'hired'));
CREATE INDEX jtest_ext_hired2_idx ON jtest ((ext->>'hired')) WHERE ext ? 'hired';
第二个指数比第一个小得多，我不确定第一个指数是好的。
问题2：当我用ext->>'hired' >= '2020-06-01'执行查询时，它使用了第一个查询，但在我的测试中，有15M行(只返回18k行(。所以这是第一个困惑——我不想在fiddle上重新创建我的内部测试(它会执行太长时间(应该更具体——但无论出于什么原因，都要使用顺序扫描。为什么它在更大的桌子上使用顺序扫描？
答案#2：在运行ANALYZE之后，它确实做到了，并且变得很快。由于这不是最重要的问题，我在这里直接回答。
最后，毫无疑问，有了额外的AND ext ? 'hired'，它可以很好地使用jtest_ext_hired2_idx索引(无论是在小提琴中还是在我的大得多的表中(。
问题3：相当笼统，这是正确的方法吗？如果我希望对JSONB中的值使用比较和LIKE操作，我可以用额外的函数索引来覆盖它吗？对于我们的情况来说，它似乎仍然比添加自定义列或连接扩展表更灵活，但它在未来不会影响我们吗？
如手册所述，GIN索引仅支持运算符：?、?&、?|、@>、@?、@@。因此，通过添加(看似无用的(ext?'hired'条件，可以使优化器使用GIN索引(而不是函数索引(。
为了索引雇佣日期，我会创建一个函数，提取值作为合适的日期。不能在索引表达式中使用强制转换，因为强制转换不是不可变的。但是，正如我们所知，yyyy-mm-dd的强制转换确实是不可变的，创建一个标记为不可变的函数并没有错。
create function hire_date(p_input jsonb)
returns date
as
$$
select (p_input ->> 'hired')::date;
$$
language sql
strict
immutable;
然后你可以使用：
CREATE INDEX jtest_ext_hired1_idx ON jtest ( (hire_date(ext)) );
当函数在where子句中使用时，该索引将直接使用：
select *
from jtest
where hire_date(ext) >= '2020-06-01';
当然，如果键'hire_date'实际上没有包含正确的DATE值，那么这将失败(但由于索引无法更新，它在插入过程中已经失败了(。
LIKE表达式的索引通常很棘手，但如果您只有左锚定的搜索字符串(like 'foo%'(，则可以使用常规的b-tree索引：
create index jtest_email on jtest ( (ext ->> 'email') varchar_pattern_ops);
要使用右锚定搜索字符串(like '%foo%'(为LIKE表达式编制索引，您需要一个三元索引。