时间戳

首页 > 解决方案 > YugabyteDB 未在 json 列上使用表达式索引

yugabyte-db - YugabyteDB 未在 json 列上使用表达式索引

问题描述

[YugabyteDB用户提出的问题]

我很难让查询计划器使用以下查询中的索引：

postgres=# create table books(k int primary key, doc jsonb not null);
postgres=# CREATE INDEX books_year
     ON books (((doc->>'year')::int) ASC)
     WHERE doc->>'year' is not null;

postgres=# EXPLAIN select
   (doc->>'ISBN')::bigint as isbn,
   doc->>'title'          as title,
   (doc->>'year')::int    as year
 from books
 where (doc->>'year')::int > 1850
 order by 3;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Sort  (cost=177.33..179.83 rows=1000 width=44)
   Sort Key: (((doc ->> 'year'::text))::integer)
   ->  Seq Scan on books  (cost=0.00..127.50 rows=1000 width=44)
         Filter: (((doc ->> 'year'::text))::integer > 1850)
(4 rows)

在按字符串值查询时，看起来它正在使用它：

postgres=# EXPLAIN select
   (doc->>'ISBN')::bigint as isbn,
   doc->>'title'          as title,
   (doc->>'year')::int    as year
 from books
 where (doc->>'year') = '1988'
 order by 3;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using books_year on books  (cost=0.00..125.50 rows=1000 width=44)
   Filter: ((doc ->> 'year'::text) = '1988'::text)
(2 rows)

标签： yugabyte-db

解决方案

索引和查询上的谓词必须匹配如下：

postgres=# CREATE INDEX books_year ON books (((doc->>'year')::int) asc) where doc->>'year' is not null;
postgres=# EXPLAIN select
   (doc->>'ISBN')::bigint as isbn,
   doc->>'title'          as title,
   (doc->>'year')::int    as year
from books
where (doc->>'year')::int > 1850 and doc->>'year' is not null;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Scan using books_year on books  (cost=0.00..5.24 rows=10 width=44)
   Index Cond: (((doc ->> 'year'::text))::integer > 1850)
(2 rows)

推荐阅读