В таблице tweets сохранены оригинальные объекты JSON, полученные от Twitter API.

Операции выделения части объекта JSON сами по себе не индексируются:

=> EXPLAIN(costs off,analyze)
SELECT * FROM tweets WHERE tweet->>'lang' = 'ru';

                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on tweets (actual time=117.791..14187.916 rows=133 loops=1)
   Filter: ((tweet ->> 'lang'::text) = 'ru'::text)
   Rows Removed by Filter: 582510
 Planning time: 4.655 ms
 Execution time: 14189.854 ms
(5 rows)

Хотя никто не мешает построить обычное B-дерево по выражению:

=> CREATE INDEX tweets_tweet_btree ON tweets((tweet->>'lang'));

=> EXPLAIN(costs off,analyze)
SELECT * FROM tweets WHERE tweet->>'lang' = 'ru';

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tweets (actual time=0.053..0.237 rows=133 loops=1)
   Recheck Cond: ((tweet ->> 'lang'::text) = 'ru'::text)
   Heap Blocks: exact=128
   ->  Bitmap Index Scan on tweets_tweet_btree (actual time=0.033..0.033 rows=133 loops=1)
         Index Cond: ((tweet ->> 'lang'::text) = 'ru'::text)
 Planning time: 0.070 ms
 Execution time: 0.289 ms
(7 rows)

Класс операторов jsonb_ops

Конечно, создавать отдельные индексы для каждого поля неразумно. Поэтому лучшим вариантом является GIN-индекс.

=> CREATE INDEX IF NOT EXISTS
tweets_tweet_gin ON tweets USING gin(tweet);

NOTICE:  relation "tweets_tweet_gin" already exists, skipping
CREATE INDEX

(Индекс создан заранее, потому что это занимает достаточно много времени.)

=> EXPLAIN(costs off,analyze)
SELECT * FROM tweets WHERE tweet ? 'possibly_sensitive';

                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tweets (actual time=103.699..18514.642 rows=273489 loops=1)
   Recheck Cond: (tweet ? 'possibly_sensitive'::text)
   Rows Removed by Index Recheck: 185703
   Heap Blocks: exact=44790
   ->  Bitmap Index Scan on tweets_tweet_gin (actual time=92.015..92.015 rows=459192 loops=1)
         Index Cond: (tweet ? 'possibly_sensitive'::text)
 Planning time: 5.136 ms
 Execution time: 18641.401 ms
(8 rows)

Почему так много ложных результатов? В твит-объект могут быть вложены другие твиты - они находятся по индексу, но отсеиваются при перепроверке, так как ключ не на верхнем уровне.

Поскольку оператор "?" проверяет ключ только на верхнем уровне, такой запрос не будет использовать индекс:

=> EXPLAIN(costs off,analyze)
SELECT * FROM tweets WHERE tweet #> '{coordinates}' ? 'type';

                             QUERY PLAN                              
---------------------------------------------------------------------
 Seq Scan on tweets (actual time=7.766..13720.704 rows=3626 loops=1)
   Filter: ((tweet #> '{coordinates}'::text[]) ? 'type'::text)
   Rows Removed by Filter: 579017
 Planning time: 0.645 ms
 Execution time: 13726.713 ms
(5 rows)

Однако в этом случае можно создать GIN-индекс по выражению:

=> CREATE INDEX tweets_tweet_gin_part ON tweets USING GIN((tweet #> '{coordinates}'));

=> EXPLAIN(costs off,analyze)
SELECT * FROM tweets WHERE tweet #> '{coordinates}' ? 'type';

                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tweets (actual time=1.139..68.531 rows=3626 loops=1)
   Recheck Cond: ((tweet #> '{coordinates}'::text[]) ? 'type'::text)
   Heap Blocks: exact=2638
   ->  Bitmap Index Scan on tweets_tweet_gin_part (actual time=0.713..0.713 rows=3626 loops=1)
         Index Cond: ((tweet #> '{coordinates}'::text[]) ? 'type'::text)
 Planning time: 0.079 ms
 Execution time: 69.527 ms
(7 rows)

Класс операторов jsonb_path_ops

=> CREATE INDEX IF NOT EXISTS
tweets_tweet_path ON tweets USING gin(tweet jsonb_path_ops);

NOTICE:  relation "tweets_tweet_path" already exists, skipping
CREATE INDEX

План запроса с использованием оператора "@>":

=> EXPLAIN(costs off,analyze)
SELECT * FROM tweets WHERE tweet @> '{"lang": "ru"}'::jsonb;

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tweets (actual time=0.094..5.401 rows=133 loops=1)
   Recheck Cond: (tweet @> '{"lang": "ru"}'::jsonb)
   Heap Blocks: exact=128
   ->  Bitmap Index Scan on tweets_tweet_path (actual time=0.034..0.034 rows=133 loops=1)
         Index Cond: (tweet @> '{"lang": "ru"}'::jsonb)
 Planning time: 0.099 ms
 Execution time: 5.473 ms
(7 rows)

Это то же самое, что и условие "tweet->>'lang' = 'ru'", которое мы раньше ускоряли с помощью отдельного B-дерева по выражению.

Поиск выполняется эффективнее, чем с классом операторов jsonb_ops.

=> SELECT pg_size_pretty(pg_total_relation_size('tweets_tweet_gin')) AS gin_ops,
pg_size_pretty(pg_total_relation_size('tweets_tweet_path')) AS gin_path_ops;

 gin_ops | gin_path_ops 
---------+--------------
 641 MB  | 444 MB
(1 row)

Индексирование JSON

Класс операторов jsonb_ops

Класс операторов jsonb_path_ops