yugabyte/yugabyte-db

[YCQL] Inaccuracy in Explain query involving Jsonb

Open

#4,529 建立於 2020年5月20日

在 GitHub 查看
 (0 留言) (0 反應) (0 負責人)C (1,003 fork)batch import
area/ycqlgood first issuekind/enhancementpriority/medium

倉庫指標

Star
 (8,229 star)
PR 合併指標
 (平均合併 17天 21小時) (30 天內合併 92 個 PR)

描述

Jira Link: DB-4671

ycqlsh:mykeyspace> describe test4;

CREATE TABLE mykeyspace.test4 (
    id bigint PRIMARY KEY,
    detail jsonb
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'true'};
CREATE INDEX index_test4_by_detail_hello_array ON mykeyspace.test4 (detail->'0'->>'hello', id)
    WITH transactions = {'enabled': 'true'};
CREATE INDEX index_test4_by_detail_foo_array ON mykeyspace.test4 (detail->'0'->>'foo', id)
    WITH transactions = {'enabled': 'true'};

ycqlsh:mykeyspace> explain SELECT id FROM mykeyspace.test4 WHERE (detail->0->>'hello') = 'world';

 QUERY PLAN
----------------------------------------------------------------------------------------
 Index Only Scan using mykeyspace.index_test4_by_detail_hello_array on mykeyspace.test4
   Key Conditions: (detail->'0'->>'hello' = 'world')

Note that the query was for detail->0->>'hello'. But the explain plan shows detail->'0'->>'hello'.

  • ->0-> would denote indexing into an array in the json
  • ->'0'-> would denote selecting a sub-field "0" in the json

貢獻者指南