PostgreSQL 運算符使用索引但底層函式不使用

我正在嘗試JSONB與 JDBC 一起使用，這意味著我必須避免使用任何使用“？”的運算符。字元（因為 PostgreSQL JDBC 驅動程序對此字元沒有轉義）。取一張簡單的表格：

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES
   ('{"name":"First","tags":["foo"]}')
 , ('{"name":"Second","tags":["foo","bar"]}')
 , ('{"name":"Third","tags":["bar","baz"]}')
 , ('{"name":"Fourth","tags":["baz"]}');
CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

使用命令行，我可以執行一個簡單的選擇，並按預期使用索引：

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

                                                           QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on jsonthings  (cost=113.50..30236.13 rows=10000 width=61) (actual time=0.024..0.025 rows=1 loops=1)
  Recheck Cond: ((d -> 'name'::text) ? 'First'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_jsonthings_name  (cost=0.00..111.00 rows=10000 width=0) (actual time=0.015..0.015 rows=1 loops=1)
        Index Cond: ((d -> 'name'::text) ? 'First'::text)
Planning time: 0.073 ms
Execution time: 0.047 ms
(7 rows)

由於我無法使用該?字元，因此我使用了支持運算符的功能?。但是，它沒有使用索引：

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE jsonb_exists(d->'name','First');
                                                           QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=3333334 width=61) (actual time=0.016..3135.119 rows=1 loops=1)
  Filter: jsonb_exists((d -> 'name'::text), 'First'::text)
  Rows Removed by Filter: 10000003
Planning time: 0.051 ms
Execution time: 3135.138 ms
(5 rows)

為什麼會發生這種情況，我該怎麼做才能使函式使用索引？請注意，實際上該表中還有另外 10MM 行，我也已enable_seqscan關閉，所以這不是計劃者決定不使用索引的情況。

為了回應評論，我嘗試使用自定義運算符：

CREATE OPERATOR ### (
 PROCEDURE = jsonb_exists,
 LEFTARG = jsonb,
 RIGHTARG = text,
 RESTRICT = contsel,
 JOIN = contjoinsel);

但這有同樣的問題：

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ### 'First';
                                                          QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=10000 width=61) (actual time=0.012..3381.608 rows=1 loops=1)
  Filter: ((d -> 'name'::text) ### 'First'::text)
  Rows Removed by Filter: 10000003
Planning time: 0.046 ms
Execution time: 3381.623 ms
(5 rows)

更新

最新的 PostgreSql 驅動程序（截至 2015 年 3 月）具有轉義?字元的能力，因此這種特定情況不再是問題。

支持的運算符和解決方法

json列上 GIN 索引的預設運算符類jsonb_ops僅支持這些運算符（每個文件）：

Name        Indexed Data Type   Indexable Operators
...
jsonb_ops   jsonb               ? ?& ?| @>

您可以通過相反的方式實現這一點：使用運算符創建一個簡單的IMMUTABLESQL函式?，該函式可以內聯，並且將像運算符本身一樣使用索引：

CREATE OR REPLACE FUNCTION jb_contains(jsonb, text)
 RETURNS bool AS
'SELECT $1 ? $2' LANGUAGE sql IMMUTABLE;

這行得通，我在 Postgres 9.4 中測試過……

誤解

但是，您一直在問錯誤的問題。你的問題有兩個基本的誤解。

1. jsonb運算符不能?用於搜尋值。僅適用於鍵或數組元素。手冊：

描述：

該字元串是否作為 JSON 值中的頂級鍵存在？

你得到了錯誤的操作符，WHERE條件不能工作：WHERE d->'name' ? 'First' 2. 無論哪種方式，您擁有的表達式索引都沒有意義

CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

• 表達式d->'name'返回一個jsonb值。您需要將值作為.d->>'name'``text
• 但這仍然毫無意義。由於name鍵的值是一個簡單的字元串，因此 GIN 索引（儘管可能）從一開始就沒有任何意義。

解決方案

您不需要操作員?- 所以也沒有解決方法。

以下是兩種實際可行的方法：

1. 普通 GIN 索引d並使用“包含”運算符**@>**：

CREATE INDEX idx_jsonthings_d_gin ON jsonthings USING GIN (d);

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}'

您甚至可以使用更專業的操作符類jsonb_path_ops。看：

• 如何從 PostgreSQL 中的 jsonb 數組中獲取特定對象？

2. B-tree 表達式索引d->>'email'並使用 good old 進行測試**=**：

CREATE INDEX idx_jsonthings_d_email ON jsonthings ((d->>'email'));

SELECT d FROM jsonthings WHERE d->>'email' = 'First';

第二個索引會小很多，查詢速度會更快。

引用自：https://dba.stackexchange.com/questions/90002