[{"event_slug":"test_1","start_time":"2014-10-08","end_time":"2014-10-12"},{"event_slug":"test_2","start_time":"2013-06-24","end_time":"2013-07-02"},{"event_slug":"test_3","start_time":"2014-03-26","end_time":"2014-03-30"}]
我正在执行以下查询：
SELECT * FROM locations
WHERE EXISTS (
SELECT 1 FROM jsonb_array_elements(events) AS e
WHERE (
e->>'event_slug' = 'test_1' AND
(
e->>'start_time' >= '2014-10-30 14:04:06 -0400' OR
e->>'end_time' >= '2014-10-30 14:04:06 -0400'
)
)
)
如何为上述的查询创建一个数据索引？这是否合理设计了几百万行,每行包含该列中的〜10个事件？
值得注意的是,我似乎仍然在顺利扫描：
CREATE INDEX events_gin_idx ON some_table USING GIN (events);
我猜这是因为我在查询中的第一件事是将数据转换为json数组元素.
解决方法
[{"event_slug":"test_1","end_time":"2014-03-30"}]
对第一个数组元素的有效测试将是：
WHERE e->0->>'event_slug' = 'test_1'
但是您可能不想将搜索限制在数组的第一个元素中.使用Postgres 9.4中的jsonb数据类型,您可以使用其他操作符和索引支持.要索引数组的元素,您需要一个GIN索引.
GIN索引的内置操作符类不支持“大于”或“小于”运算符> > =< < =.这也适用于jsonb,您可以在两个运算符类之间进行选择. Per documentation：
Name Indexed Data Type Indexable Operators ... jsonb_ops jsonb ? ?& ?| @> jsonb_path_ops jsonb @>
(jsonb_ops是默认值).您可以覆盖相等性测试,但这两个操作符都不涉及> =比较的要求.你需要一个btree索引.
基本解决方案
支持使用索引进行相等检查：
CREATE INDEX locations_events_gin_idx ON locations
USING gin (events jsonb_path_ops);
SELECT * FROM locations WHERE events @> '[{"event_slug":"test_1"}]';
如果过滤器具有足够的选择性,这可能是足够好的.
假设end_time> = start_time,所以我们不需要两个检查.只检查end_time是更便宜和同等的：
SELECT l.*
FROM locations l,jsonb_array_elements(l.events) e
WHERE l.events @> '{"event_slug":"test_1"}'
AND (e->>'end_time')::timestamp >= '2014-10-30 14:04:06 -0400'::timestamptz;
利用隐式JOIN LATERAL.细节(最后一章)：
> PostgreSQL unnest() with element number
小心不同的数据类型！ JSON值中的内容看起来像时间戳[没有时区],而你的谓词使用时区文字的时间戳.时间戳值根据当前时区设置进行解释,而给定的timestamptz文字必须明确地转换为timestamptz,否则时区将被忽略！以上查询应按要求工作.详细说明：
> Ignoring timezones altogether in Rails and PostgreSQL
jsonb_array_elements()的更多解释：
> PostgreSQL joining using JSONB
高级解决方案
如果上述不够好,我会考虑一个MATERIALIZED VIEW存储规范化形式的相关属性.这允许简单的btree索引.
建立：
CREATE TYPE event_type AS (,event_slug text,start_time timestamp,end_time timestamp ); CREATE MATERIALIZED VIEW loc_event AS SELECT l.location_id,e.event_slug,e.end_time -- start_time not needed FROM locations l,jsonb_populate_recordset(null::event_type,l.events) e;
jsonb_populate_recordset()的相关答案：
> How to convert PostgreSQL 9.4’s jsonb type to float
CREATE INDEX loc_event_idx ON loc_event (event_slug,end_time,location_id);
还包括location_id以允许仅索引扫描. (见manual page和Postgres Wiki)
查询：
SELECT * FROM loc_event WHERE event_slug = 'test_1' AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;
或者,如果您需要从底层的位置表完整的行：
SELECT l.* FROM ( SELECT DISTINCT location_id FROM loc_event WHERE event_slug = 'test_1' AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz ) le JOIN locations l USING (location_id);
