Partial gin index does not work with WHERE-CodePudding

I have the following table:

CREATE TABLE m2m_entries_n_elements(
  entry_id UUID
  element_id UUID
  value JSONB
)

Value is jsonb object in following format: {<type>: <value>}

And I want to create GIN index only for number values:

CREATE INDEX IF NOT EXISTS idx_element_value_number
ON m2m_entries_n_elements
USING GIN (element_id, CAST(value ->> 'number' AS INT))
WHERE value ? 'number';

But when I use EXPLAIN ANALYZE I see that index does not work:

EXPLAIN ANALYZE
SELECT *
FROM m2m_entries_n_elements WHERE CAST(value ->> 'number' AS INT) = 2;

Seq Scan on m2m_entries_n_elements  (cost=0.00..349.02 rows=50 width=89) (actual time=0.013..2.087 rows=1663 loops=1)
Filter: (((value ->> 'number'::text))::integer = 2)
Rows Removed by Filter: 8338
Planning Time: 0.042 ms
Execution Time: 2.150 ms

But if I remove WHERE value ? 'number' from creating the index, it starts working:

Bitmap Heap Scan on m2m_entries_n_elements  (cost=6.39..70.29 rows=50 width=89) (actual time=0.284..0.819 rows=1663 loops=1)
  Recheck Cond: (((value ->> 'number'::text))::integer = 2)
  Heap Blocks: exact=149
  ->  Bitmap Index Scan on idx_elements  (cost=0.00..6.38 rows=50 width=0) (actual time=0.257..0.258 rows=1663 loops=1)
  Index Cond: (((value ->> 'number'::text))::integer = 2)
Planning Time: 0.207 ms
Execution Time: 0.922 ms

CodePudding user response：

PostgreSQL does not have a general theorem prover. Maybe you intuit that value ->> 'number' being defined implies that value ? 'number' is true, but PostgreSQL doesn't know that. You would need to explicitly include the ? condition in your query to get use of the index.