PostgreSQL中的多列GIN索引不对`preference` jsonb列执行索引扫描

axr492tv 于 2024-01-07 发布在 PostgreSQL

关注(0)|答案(1)|浏览(158)

我有一个preference列，其类型为JSONB。

{
    "slotStatus": {
        "reason": "Weather",
        "status": "CANCELLED",
        "statusMeta": "RE"
    },
    "registration": {
        "restriction": "perEvent",
        "maxRegistration": 20
    }
}

字符串
当我为下面的查询执行Explain analyze时，只有event_id被索引扫描。但是preference列中的status字段没有被索引扫描。这导致执行时间更长（大约45到50秒）

查询一：

Explain analyze SELECT s.preference, s.data FROM mv_view s WHERE 
    s.event_id= 'event id goes here' AND (
    NOT (s.preference::jsonb -> 'slotStatus' @> '{"status": "CANCELLED"}')
    OR s.preference::jsonb -> 'slotStatus' IS NULL

型

更新查询：

EXPLAIN ANALYZE SELECT s.preference, s.data FROM mv_view s 
    WHERE  s.event_id= 'bc94ec84-a9fe-468a-a805-2124d937c7a0' AND
    ((s.preference::json #>> '{slotStatus,status}') <> 'CANCELLED' or (s.preference::json #>> '{slotStatus,status}') is null)

型
此表上的索引：

create index mv_view_eventid_preference_idx on mv_view  (event_id) where 
    ((preference::json #>> '{slotStatus,status}') <> 'CANCELLED'
    OR 
    (preference::json #>> '{slotStatus,status}') is null);

CREATE INDEX mv_view_eventid_preference_idx1
    ON mv_view USING gin
    (event_id, (preference -> 'slotStatus'))

CREATE UNIQUE INDEX loc_slot_mv_view_slotid_idx
    ON mv_view USING btree
    (id)
    TABLESPACE pg_default;

型
您能否帮助细化查询并添加正确的索引，以确保preference列中的event_id和status字段都经过索引扫描
解释（分析）

的数据
解释（分析，详细）：

的
解释（分析，缓冲液）：

解释（分析，设置）：

postgresql

来源：https://stackoverflow.com/questions/77733826/multicolumn-gin-indexes-in-postgresql-does-not-performing-index-scans-on-the-pr

1条答案

按热度按时间

ws51t4hk1#

索引只能在被索引的对象上使用。被索引的对象是列“preference”，而不是表达式prefence::jsonb -> 'slotStatus'，所以索引不能使用。
此外，你甚至没有使用@>，你使用的是NOT ... @>。这在任何情况下都不能被索引。
最后，OR也会阻止索引的使用，即使前两个没有。
第一个可以通过改变查询或改变索引来规避，但我不认为你可以绕过第二个，所以你几乎卡住了。
忽略OR和NOT，让现有索引工作的方法是将查询更改为在顶层使用@>：

where preference @> '{"slotStatus" : {"status": "CANCELLED"}}'

字符串
或者创建一个新的索引来处理现有的查询（除了现有的NOT和OR部分）：

CREATE INDEX mv_view_eventid_preference_idx
ON mv_view using GIN (event_id, (preference->'slotStatus'))

型
但是一个解决方案，将与您当前的查询是一个部分索引。

create index on mv_view  (event_id) where 
    NOT (preference -> 'slotStatus' @> '{"status": "CANCELLED"}') 
    OR 
    preference -> 'slotStatus' IS NULL;

型
但是它是不可推广的（例如，推广到其他状态），因此可能导致需要为每种类型的查询使用一个索引。

赞(0）回复(0）举报 2024-01-07

我来回答

PostgreSQL中的多列GIN索引不对`preference` jsonb列执行索引扫描

1条答案

相关问题

热门标签

最新问答