On Jan 20, 2024, at 11:45, Tom Lane <t...@sss.pgh.pa.us> wrote: > You sure about that? It would surprise me if we could effectively use > a not-equal condition with an index. If it is only == that works, > then the preceding statement seems sufficient.
I’m not! I just assumed it in the same way creating an SQL = operator automatically respects NOT syntax (or so I recall). In fiddling a bit, I can’t get it to use an index: CREATE TABLE MOVIES (id SERIAL PRIMARY KEY, movie JSONB NOT NULL); \copy movies(movie) from PROGRAM 'curl -s https://raw.githubusercontent.com/prust/wikipedia-movie-data/master/movies.json | jq -c ".[]" | sed "s|\\\\|\\\\\\\\|g"'; create index on movies using gin (movie); analyze movies; david=# explain analyze select id from movies where movie @? '$ ?(@.genre[*] != "Teen")'; QUERY PLAN ----------------------------------------------------------------------------------------------------- Seq Scan on movies (cost=0.00..3741.41 rows=4 width=4) (actual time=19.222..19.223 rows=0 loops=1) Filter: (movie @? '$?(@."genre"[*] != "Teen")'::jsonpath) Rows Removed by Filter: 36273 Planning Time: 1.242 ms Execution Time: 19.247 ms (5 rows) But that might be because the planner knows that the query is going to fetch most records, anyway. If I set most records to a single value: david=# update movies set movie = jsonb_set(movie, '{year}', '2020'::jsonb) where id < 3600; UPDATE 3599 david=# analyze movies; ANALYZE david=# explain analyze select id from movies where movie @? '$ ?(@.year != 2020)'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on movies (cost=0.00..3884.41 rows=32609 width=4) (actual time=0.065..43.730 rows=32399 loops=1) Filter: (movie @? '$?(@."year" != 2020)'::jsonpath) Rows Removed by Filter: 3874 Planning Time: 1.759 ms Execution Time: 45.368 ms (5 rows) Looks like it still doesn’t use the index with !=. Pity. Best, David
