dump.sql

drop table datasets;
create table datasets ( uuid varchar(40), name varchar(100), data jsonb );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '212ea202-36d9-4f7b-b2ff-58a702e7fa5e', 'books',
      json_build_object(
        'id_integer',    2602,    'id_string',    '2602',    'id_float',    2602.0,
        'title_integer', 0, 'title_string', 'ぅえりはわへうせぴぽしぷ', 'title_float', 0.0,
        'price_integer', 660, 'price_string', '660', 'price_float', 660.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'd18fc8a7-cef1-4dc9-b64d-04fa23c2d516', 'books',
      json_build_object(
        'id_integer',    4194,    'id_string',    '4194',    'id_float',    4194.0,
        'title_integer', 0, 'title_string', 'むひどゐぎびもねゃづいす', 'title_float', 0.0,
        'price_integer', 626, 'price_string', '626', 'price_float', 626.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '31dc6beb-2109-488d-a818-2b0ef52e2091', 'users',
      json_build_object(
        'id_integer',       3286,       'id_string',       '3286',       'id_float',       3286.0,
        'name_integer',     0,     'name_string',     'ぷしけぐすあざっえたかげ',     'name_float',     0.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '24390ac5-c42f-4522-b939-15a010b97d35', 'books',
      json_build_object(
        'id_integer',    2807,    'id_string',    '2807',    'id_float',    2807.0,
        'title_integer', 0, 'title_string', 'どてゃぷぇさぱたゆうゎお', 'title_float', 0.0,
        'price_integer', 825, 'price_string', '825', 'price_float', 825.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'ba5f6d2e-5e86-4da6-99ce-2f1c1dced51e', 'users',
      json_build_object(
        'id_integer',       8640,       'id_string',       '8640',       'id_float',       8640.0,
        'name_integer',     0,     'name_string',     'へでょゑらいくぷみぢぜゆ',     'name_float',     0.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'e40474d8-7cd3-4c0f-a976-60c7d289ee13', 'books',
      json_build_object(
        'id_integer',    2893,    'id_string',    '2893',    'id_float',    2893.0,
        'title_integer', 0, 'title_string', 'ぶらぼるれすけきはさぺし', 'title_float', 0.0,
        'price_integer', 557, 'price_string', '557', 'price_float', 557.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'e3ddf69d-940d-45db-93bc-bedee1147f24', 'books',
      json_build_object(
        'id_integer',    3873,    'id_string',    '3873',    'id_float',    3873.0,
        'title_integer', 0, 'title_string', 'ねぜちそどぎけぽめはきえ', 'title_float', 0.0,
        'price_integer', 593, 'price_string', '593', 'price_float', 593.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '5c4b3d2c-25b2-4849-8f5a-8750e6f400a2', 'todo',
      json_build_object(
        'id_integer',       465,       'id_string',       '465',       'id_float',       465.0,
        'what_integer',     0,     'what_string',     'てぬなるぷのいびつわじず',     'what_float',     0.0,
        'priority_integer', 1, 'priority_string', '1', 'priority_float', 1.0,
        'assignees_array',  json_build_array('9d07964a-7ade-4c19-acde-bc271a5bc213', '165858e2-a969-45b0-90ec-9908bf5f7fd7', 'c53eb57b-b554-4651-b5c2-e376064874e2')
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'a11ac9a3-61cb-4d76-9072-4b33cbd2ae9d', 'books',
      json_build_object(
        'id_integer',    3393,    'id_string',    '3393',    'id_float',    3393.0,
        'title_integer', 0, 'title_string', 'えぢぅそなねぎちつぇぜた', 'title_float', 0.0,
        'price_integer', 15, 'price_string', '15', 'price_float', 15.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'bd8f2461-9cf7-4bec-9846-ad6a6d3fa89c', 'users',
      json_build_object(
        'id_integer',       2257,       'id_string',       '2257',       'id_float',       2257.0,
        'name_integer',     0,     'name_string',     'とどぺしろぢぼそまほぃり',     'name_float',     0.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '3867de6c-3ff9-4c0b-a32f-7a8a2aaad714', 'users',
      json_build_object(
        'id_integer',       6143,       'id_string',       '6143',       'id_float',       6143.0,
        'name_integer',     0,     'name_string',     'ふむないぉろょゐぽぼれへ',     'name_float',     0.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '14065cb4-1f6f-4885-a2d4-00b4a8f62ec1', 'users',
      json_build_object(
        'id_integer',       7088,       'id_string',       '7088',       'id_float',       7088.0,
        'name_integer',     0,     'name_string',     'ぺらゐよぃょさぅぉあづた',     'name_float',     0.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( 'b364de53-307b-4bb7-a187-3ea0cd83087d', 'books',
      json_build_object(
        'id_integer',    3715,    'id_string',    '3715',    'id_float',    3715.0,
        'title_integer', 0, 'title_string', 'がこのぷいぶとそりむらも', 'title_float', 0.0,
        'price_integer', 147, 'price_string', '147', 'price_float', 147.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '4f1d2369-ddc0-4cff-8f36-c23811a7bba8', 'books',
      json_build_object(
        'id_integer',    6631,    'id_string',    '6631',    'id_float',    6631.0,
        'title_integer', 0, 'title_string', 'ぬぷあせおゃひぜきをゅづ', 'title_float', 0.0,
        'price_integer', 582, 'price_string', '582', 'price_float', 582.0
        )
      );
    INSERT INTO datasets ( uuid, name, data ) VALUES ( '21c05231-3060-4f44-a7a4-cd9660eef283', 'users',
      json_build_object(
        'id_integer',       1471,       'id_string',       '1471',       'id_float',       1471.0,
        'name_integer',     0,     'name_string',     'おぴみけぽゃきぢまほびぉ',     'name_float',     0.0

result.md

create table schemas ( name varchar(100), schema varchar(1000) );
INSERT INTO schemas ( name, schema ) VALUES ( 'books', '
{
  "id": "integer",
  "title": "string",
  "price": "integer"
}
' );
INSERT INTO schemas ( name, schema ) VALUES ( 'users', '
{
  "id": "integer",
  "name": "string",
}
' );
INSERT INTO schemas ( name, schema ) VALUES ( 'todo', '
{
  "id": "integer",
  "what": "string",
  "priority": "integer",
}
' );

SELECT data FROM datasets WHERE name = 'todo' and
  (data->>'assignees_array')::jsonb ? 'b456e596-9028-47a0-b87a-f8b23c443eee' and
  (data->>'what_string')::TEXT LIKE '%あ%'
  ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT;
                                                                                                                                                                               data
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id_float": 1722.0, "id_string": "1722", "id_integer": 1722, "what_float": 0.0, "what_string": "りあぃふたぎどぐぽこまを", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["b456e596-9028-47a0-b87a-f8b23c443eee", "bf82082f-7b61-443e-bb6d-1fa1197ce911", "f1ddbb9d-ed35-4dd4-857f-082b142a6a44"], "priority_string": "2", "priority_integer": 2}
(1 row)

SELECT data FROM datasets WHERE name = 'todo' and (data->>'what_string')::TEXT LIKE '%あ%' ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT ASC LIMIT 25 OFFSET 50;
                                                                                                                                                                               data
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id_float": 1179.0, "id_string": "1179", "id_integer": 1179, "what_float": 0.0, "what_string": "ぼそかくまぱぃあてべはご", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["29f91ab9-3e40-4e01-b990-8f03d23dab50", "b3bdc369-5549-4bb6-8a19-0c9487162085", "9d665950-6c45-4b77-b1b7-de35b69de646"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1183.0, "id_string": "1183", "id_integer": 1183, "what_float": 0.0, "what_string": "せゃよぺざるぢべむびへあ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["b86e6da5-c799-46ba-a4ff-e6e46a5295da", "5e33425d-ce7b-4be5-b6f2-88abcba38a7c", "82aa1be5-8225-4697-ad0e-18e266d30e0c"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1202.0, "id_string": "1202", "id_integer": 1202, "what_float": 0.0, "what_string": "おとゆょりぢへあむつげぞ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["d8e0021c-44cb-4b35-a830-a06bf9b3d225", "74de866e-a393-4ea6-a12f-8f7365e0a124", "5a7c2827-44b6-4348-a927-bc8f27f58926"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1228.0, "id_string": "1228", "id_integer": 1228, "what_float": 0.0, "what_string": "どほけよぬゑあさやずわぺ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["620c5a08-9bcb-4f3a-9208-bf6343bedbb4", "e24e9bb0-b40f-4263-9f96-2e3f3256b8c6", "3526eb5b-2b36-4d2a-b96c-2d397c002b2e"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1245.0, "id_string": "1245", "id_integer": 1245, "what_float": 0.0, "what_string": "えぷすゎるにゑでろあぼぶ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["852f499e-af07-45d4-9c28-576b65ff6d02", "aa4bdfa7-c233-4386-bdf5-e7533ecc1649", "9540daf3-75a7-4cc8-b67c-1b6e8592ecbe"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1257.0, "id_string": "1257", "id_integer": 1257, "what_float": 0.0, "what_string": "ぢづしめんじるれあらわず", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["9d455bc4-4659-4ba5-81f2-c7f4a2e586b0", "f0807e47-09a9-4a0a-8e1b-9a9942ce91ba", "4a29fce4-3111-4e9f-bacd-d5dbcc0eb303"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1264.0, "id_string": "1264", "id_integer": 1264, "what_float": 0.0, "what_string": "むぴるうあおっだちょづな", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["0215072b-ecd6-47dc-b427-3868cbd40774", "73a20356-e05b-42fc-87e3-87b58d0bbe1d", "5069ddf2-80e0-4c77-8468-d6e7d1f0878a"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1277.0, "id_string": "1277", "id_integer": 1277, "what_float": 0.0, "what_string": "びらあだほぽにとえゆいぶ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["9e3df5c9-c7cf-4694-835d-7380aa91eb75", "bb07ec3b-726e-4954-bead-dfbae05c2cde", "00a3cd96-1442-4662-bdfb-b1af6607ca37"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1290.0, "id_string": "1290", "id_integer": 1290, "what_float": 0.0, "what_string": "あはらろずふなさいぽぎぅ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["5e4a1c6c-f7fa-41fb-b604-392ee763aeae", "ad634976-499f-4e0c-8a3e-fb114a1efbe9", "c8d2995d-9ebb-4eb0-9e2d-6e87c117c147"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1292.0, "id_string": "1292", "id_integer": 1292, "what_float": 0.0, "what_string": "ぽあむどすげぐやでれもぼ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["9bdf460f-e102-4d17-aa07-79058ff8b958", "36094664-b1d0-4127-ab51-701d3423673a", "1e44dc91-3253-4c22-b538-07d542841c73"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1294.0, "id_string": "1294", "id_integer": 1294, "what_float": 0.0, "what_string": "れにびづせいきあゅぐまゆ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["11c78b91-aca8-44b8-9722-3506a22a99bc", "47fc1a70-3b62-4b9b-ba4b-aa28045c3ff3", "0d8d396b-1ff1-4dc0-b64d-bcc0a5cc9432"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1350.0, "id_string": "1350", "id_integer": 1350, "what_float": 0.0, "what_string": "もねわぐいゑぢきまべあむ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["21e7961b-6b9e-40a3-aca8-e75339ce0800", "272db317-1da1-4ee7-b29a-b594225d2538", "aa1d2601-16d6-42ac-9d3e-ee3b2d5b598d"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1373.0, "id_string": "1373", "id_integer": 1373, "what_float": 0.0, "what_string": "ぽどあぷげぞぇりっこゎぃ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["12998a1a-b655-48bb-a76a-5f641f7998fc", "33b8ca05-90d5-4274-b267-139770f69dd5", "1fdbf432-eab5-45dd-89ea-5661f506c5c5"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1395.0, "id_string": "1395", "id_integer": 1395, "what_float": 0.0, "what_string": "みぺだんねにれあそぶのと", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["35b0f1bf-588a-4f31-90c1-b7bb51083c4c", "bacc5096-1c9e-4c98-90de-8067b9e70977", "87ca34b3-c743-4c24-85af-af2594ef0a0b"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1398.0, "id_string": "1398", "id_integer": 1398, "what_float": 0.0, "what_string": "ねにゃえふよてとおぞあち", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["83567373-54c4-46f3-b669-fe97c05ff942", "e8a553dc-4d74-4f9b-8b7a-d77cd9e8238e", "73cb9978-b255-4e4d-b13d-ccd5b1d63ead"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1413.0, "id_string": "1413", "id_integer": 1413, "what_float": 0.0, "what_string": "あごぎきっおへべこもぉぅ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["b3c32759-d84c-4265-969c-0da0bc8341a1", "a501d186-30b4-4675-89f5-7946373210c4", "1aeedf0b-f3ba-4d17-8b53-bfb4118da11f"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1446.0, "id_string": "1446", "id_integer": 1446, "what_float": 0.0, "what_string": "じさぃごづぶゑれぱゅあい", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["39127ce3-177e-41a1-958e-d9b591c3c2f3", "4c5c3b3f-1f3b-4df2-a47c-b799f4a27c28", "5043c0f2-2eba-485c-bcd2-0079d55683da"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1475.0, "id_string": "1475", "id_integer": 1475, "what_float": 0.0, "what_string": "にらりあをゅゆてはんこど", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["89ac3419-caa5-4954-9286-e36cc0f2037a", "efee32be-f0b1-4e92-968f-da7f43585359", "f707230c-9b5d-4676-86ac-d3d1868f31ab"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1504.0, "id_string": "1504", "id_integer": 1504, "what_float": 0.0, "what_string": "ぱぷまもぐょひのにあほご", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["b644dffb-e3a6-426d-8ce8-6cf5d32832f8", "7ed260f1-f81c-40cc-83cb-1f37cddecbc1", "7e699c2c-c77c-4934-9113-fe0a0f74c9e8"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1525.0, "id_string": "1525", "id_integer": 1525, "what_float": 0.0, "what_string": "ひづあもぜゎでけがのぺぽ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["47062f09-61e2-4f6e-b091-114187b5934b", "270c171c-d544-4709-b95c-5f745574b2e3", "e1505a34-b4ec-4c4c-bec8-4f58e89a8504"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1529.0, "id_string": "1529", "id_integer": 1529, "what_float": 0.0, "what_string": "ぽりげじこっへづめだどあ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["8c5fdf8d-4451-43a3-ae63-773244d0229c", "12e41694-c012-420b-af03-99609a578c6c", "5181e52a-7002-4bf8-9477-7e6b708c6dd3"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1561.0, "id_string": "1561", "id_integer": 1561, "what_float": 0.0, "what_string": "だあごりせらろさぺけみひ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["3642b00e-6665-4584-9d3d-444d78950bed", "edeadaf0-ec36-4059-8396-0568a863b90a", "0c1dfdc2-5cce-460a-ab14-905644263fe2"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1633.0, "id_string": "1633", "id_integer": 1633, "what_float": 0.0, "what_string": "あぉをらばむぺくえぬいれ", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["3eaaaf69-b03b-416b-b2a9-a9a3dab10023", "2496488a-9c77-4205-b7db-d8d2ec61775a", "0befe256-17cd-481d-94e1-4df7eaa0bb7c"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1675.0, "id_string": "1675", "id_integer": 1675, "what_float": 0.0, "what_string": "あうぞびだんじねどでやす", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["8d012eb9-65c4-49d1-affb-d2de330057cb", "78a37bdd-49e6-4f0c-842d-ef088cae8a07", "524fb49c-b941-459b-88e3-8cb803e771f0"], "priority_string": "2", "priority_integer": 2}
 {"id_float": 1722.0, "id_string": "1722", "id_integer": 1722, "what_float": 0.0, "what_string": "りあぃふたぎどぐぽこまを", "what_integer": 0, "priority_float": 2.0, "assignees_array": ["b456e596-9028-47a0-b87a-f8b23c443eee", "bf82082f-7b61-443e-bb6d-1fa1197ce911", "f1ddbb9d-ed35-4dd4-857f-082b142a6a44"], "priority_string": "2", "priority_integer": 2}
(25 rows)

todo テーブルの what を %あ% で検索して order by priority して 25ページ区切り3ページ目を取得する SQL

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' and
  (data->>'what_string')::TEXT LIKE '%あ%'
  ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT ASC
  LIMIT 25 OFFSET 50;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17336.94..17339.85 rows=25 width=308) (actual time=41.854..48.218 rows=25 loops=1)
   ->  Gather Merge  (cost=17331.10..17724.30 rows=3370 width=308) (actual time=41.822..48.213 rows=75 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=16331.08..16335.29 rows=1685 width=308) (actual time=38.876..38.889 rows=113 loops=3)
               Sort Key: (((data ->> 'priority_integer'::text))::integer) DESC, (((data ->> 'id_integer'::text))::integer)
               Sort Method: quicksort  Memory: 3148kB
               ->  Parallel Seq Scan on datasets  (cost=0.00..16240.77 rows=1685 width=308) (actual time=0.023..32.369 rows=4873 loops=3)
                     Filter: (((name)::text = 'todo'::text) AND ((data ->> 'what_string'::text) ~~ '%あ%'::text))
                     Rows Removed by Filter: 95127
 Planning time: 0.058 ms
 Execution time: 48.803 ms
(12 rows)

create index datasets_name_index on datasets (name);
追加したら

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' and (data->>'what_string')::TEXT LIKE '%あ%' ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT ASC LIMIT 25 OFFSET 50;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17336.94..17339.85 rows=25 width=308) (actual time=42.964..47.993 rows=25 loops=1)
   ->  Gather Merge  (cost=17331.10..17724.30 rows=3370 width=308) (actual time=42.944..47.987 rows=75 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=16331.08..16335.29 rows=1685 width=308) (actual time=39.345..39.358 rows=117 loops=3)
               Sort Key: (((data ->> 'priority_integer'::text))::integer) DESC, (((data ->> 'id_integer'::text))::integer)
               Sort Method: quicksort  Memory: 3164kB
               ->  Parallel Seq Scan on datasets  (cost=0.00..16240.77 rows=1685 width=308) (actual time=0.031..33.155 rows=4873 loops=3)
                     Filter: (((name)::text = 'todo'::text) AND ((data ->> 'what_string'::text) ~~ '%あ%'::text))
                     Rows Removed by Filter: 95127
 Planning time: 0.071 ms
 Execution time: 48.576 ms
(12 rows)

create index datasets_data_id_index on datasets using hash ((data->>'id'));

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' and (data->>'what_string')::TEXT LIKE '%あわ%' ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT ASC LIMIT 25 OFFSET 50;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17218.99..17219.11 rows=1 width=308) (actual time=34.650..38.459 rows=25 loops=1)
   ->  Gather Merge  (cost=17215.96..17218.99 rows=26 width=308) (actual time=34.635..38.455 rows=75 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=16215.94..16215.97 rows=13 width=308) (actual time=29.604..29.605 rows=39 loops=3)
               Sort Key: (((data ->> 'priority_integer'::text))::integer) DESC, (((data ->> 'id_integer'::text))::integer)
               Sort Method: quicksort  Memory: 64kB
               ->  Parallel Seq Scan on datasets  (cost=0.00..16215.69 rows=13 width=308) (actual time=0.730..29.483 rows=52 loops=3)
                     Filter: (((name)::text = 'todo'::text) AND ((data ->> 'what_string'::text) ~~ '%あわ%'::text))
                     Rows Removed by Filter: 99948
 Planning time: 0.103 ms
 Execution time: 38.485 ms
(12 rows)

create index datasets_name_index on datasets (name);
create index datasets_uuid_index on datasets (name, uuid);
create index datasets_uuid_data_id_index on datasets using gin(data);

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' and (data->>'what_string')::TEXT LIKE '%あ%' ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT ASC LIMIT 25 OFFSET 50;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=167337.31..167340.23 rows=25 width=309) (actual time=3584.645..4065.820 rows=25 loops=1)
   ->  Gather Merge  (cost=167331.48..171245.22 rows=33544 width=309) (actual time=3584.570..4065.814 rows=75 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=166331.45..166373.38 rows=16772 width=309) (actual time=3571.117..3571.181 rows=116 loops=3)
               Sort Key: (((data ->> 'priority_integer'::text))::integer) DESC, (((data ->> 'id_integer'::text))::integer)
               Sort Method: external merge  Disk: 23368kB
               ->  Parallel Seq Scan on datasets  (cost=0.00..162746.58 rows=16772 width=309) (actual time=0.218..3342.920 rows=48627 loops=3)
                     Filter: (((name)::text = 'todo'::text) AND ((data ->> 'what_string'::text) ~~ '%あ%'::text))
                     Rows Removed by Filter: 951373
 Planning time: 0.111 ms
 Execution time: 4072.116 ms
(12 rows)

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' and (data->>'what_string')::TEXT LIKE '%あ%' LIMIT 25 OFFSET 50;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=239.89..359.83 rows=25 width=301) (actual time=0.426..0.613 rows=25 loops=1)
   ->  Seq Scan on datasets  (cost=0.00..193120.00 rows=40252 width=301) (actual time=0.040..0.608 rows=75 loops=1)
         Filter: (((name)::text = 'todo'::text) AND ((data ->> 'what_string'::text) ~~ '%あ%'::text))
         Rows Removed by Filter: 1338
 Planning time: 0.123 ms
 Execution time: 0.628 ms
(6 rows)

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' ORDER BY (data->>'priority_integer')::INT DESC, (data->>'id_integer')::INT ASC LIMIT 25 OFFSET 50;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=223504.43..223504.49 rows=25 width=309) (actual time=2301.196..2301.199 rows=25 loops=1)
   ->  Sort  (cost=223504.31..226020.06 rows=1006300 width=309) (actual time=2301.192..2301.194 rows=75 loops=1)
         Sort Key: (((data ->> 'priority_integer'::text))::integer) DESC, (((data ->> 'id_integer'::text))::integer)
         Sort Method: top-N heapsort  Memory: 88kB
         ->  Bitmap Heap Scan on datasets  (cost=18839.26..187132.51 rows=1006300 width=309) (actual time=313.186..2079.918 rows=1000000 loops=1)
               Recheck Cond: ((name)::text = 'todo'::text)
               Rows Removed by Index Recheck: 1405373
               Heap Blocks: exact=41799 lossy=98820
               ->  Bitmap Index Scan on datasets_name_index  (cost=0.00..18587.68 rows=1006300 width=0) (actual time=303.937..303.937 rows=1000000 loops=1)
                     Index Cond: ((name)::text = 'todo'::text)
 Planning time: 0.089 ms
 Execution time: 2303.084 ms
(12 rows)

EXPLAIN ANALYZE SELECT data FROM datasets WHERE name = 'todo' and (data->>'priority_integer')::INT >= 1;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on datasets  (cost=18671.54..181933.29 rows=335433 width=301) (actual time=104.435..1239.517 rows=666767 loops=1)
   Recheck Cond: ((name)::text = 'todo'::text)
   Rows Removed by Index Recheck: 1405373
   Filter: (((data ->> 'priority_integer'::text))::integer >= 1)
   Rows Removed by Filter: 333233
   Heap Blocks: exact=41799 lossy=98820
   ->  Bitmap Index Scan on datasets_name_index  (cost=0.00..18587.68 rows=1006300 width=0) (actual time=95.545..95.545 rows=1000000 loops=1)
         Index Cond: ((name)::text = 'todo'::text)
 Planning time: 0.095 ms
 Execution time: 1265.106 ms
(10 rows)