title: Optimizing GROUP BY of jsonb array values in Postgres
body: <p><strong>Updated for better hardware</strong></p>
  <p>I have the following simplified schema in Postgres12 whereby a <em>dataset</em> has many <em>cfiles</em> and each cfile has <em>property_values</em> stored as jsonb:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM cfiles;
   id | dataset_id |                property_values (jsonb)                
  ----+------------+-----------------------------------------------
    1 |          1 | {&quot;Sample Names&quot;: [&quot;SampA&quot;, &quot;SampB&quot;], (...other properties)}
    2 |          1 | {&quot;Sample Names&quot;: [&quot;SampA&quot;, &quot;SampC&quot;], (...other properties)}
    3 |          1 | {&quot;Sample Names&quot;: [&quot;SampD&quot;],          (...other properties)}
  </code></pre>
  <p>Some &quot;Sample Names&quot; arrays are large and may have up to 100 short strings (~1K chars total)</p>
  <p>I am using the query below to group by each of the values in the &quot;Sample Names&quot; jsonb arrays. The query gives the results I want but it takes around <strong>15 seconds</strong> on <strong>~45K rows</strong> (explain query plan at the bottom) running on aws t2.micro with <strong>1vCPU and 1GB ram</strong></p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
  jsonb_array_elements_text(property_values -&gt; 'Sample Names') as sample_names,
  max(cfiles.last_modified) as last_modified,
  string_agg(DISTINCT(users.email), ', ') as user_email,
  string_agg(DISTINCT(groups.name), ', ') as group_name
  FROM cfiles
  JOIN datasets ON cfiles.dataset_id=datasets.id
  LEFT JOIN user_permissions ON (user_permissions.cfile_id=cfiles.id OR user_permissions.dataset_id=datasets.id)
  LEFT JOIN users on users.id=user_permissions.user_id
  LEFT JOIN group_permissions ON (group_permissions.cfile_id=cfiles.id OR group_permissions.dataset_id=datasets.id)
  LEFT JOIN groups ON groups.id=group_permissions.group_id
  LEFT JOIN user_groups ON groups.id=user_groups.group_id
  WHERE
  cfiles.tid=5
  -- Query needs to support sample name filtering eg:
  -- AND property_values -&gt;&gt; 'Sample Names' like '%test%'
  -- As well as filtering by columns from the other joined tables
  GROUP BY sample_names
  -- Below is required for pagination
  ORDER BY &quot;sample_names&quot; desc NULLS LAST
  </code></pre>
  <p>All of the ID columns and join columns are indexed.</p>
  <p>I have tried to reduce the query further but found it tricky because it's difficult to correlate incremental reductions with improvements and my final result needs to include everything.</p>
  <p>Just the cfiles without any joins still takes ~12 seconds:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
  jsonb_array_elements_text(property_values -&gt; 'Sample Names') as sample_names,
  max(cfiles.last_modified) as last_modified
  FROM cfiles
  WHERE
  cfiles.tid=5
  GROUP BY sample_names
  ORDER BY &quot;sample_names&quot; desc NULLS LAST
  LIMIT 20
  OFFSET 0
  </code></pre>
  <p>I tried using more of a CTE pattern below, but it was slower and didn't produce the correct results</p>
  <pre class="lang-sql prettyprint-override"><code>WITH cf AS (
    SELECT
    cfiles.id as id,
    cfiles.dataset_id as dataset_id,
    jsonb_array_elements_text(property_values -&gt; 'Sample Names') as sample_names,
    cfiles.last_modified as last_modified
    FROM cfiles
    WHERE
    cfiles.tid=5
    GROUP BY sample_names, id, dataset_id, last_modified
    ORDER BY &quot;sample_names&quot; desc NULLS LAST
    LIMIT 20
    OFFSET 0
  )
  
  SELECT cf.sample_names as sample_names,
  max(cf.last_modified) as last_modified,
  string_agg(DISTINCT(users.email), ', ') as user_email,
  string_agg(DISTINCT(groups.name), ', ') as group_name
  FROM cf
  JOIN datasets ON cf.dataset_id=datasets.id
  LEFT JOIN user_permissions ON (user_permissions.cfile_id=cf.id OR user_permissions.dataset_id=datasets.id)
  LEFT JOIN users on users.id=user_permissions.user_id
  LEFT JOIN group_permissions ON (group_permissions.cfile_id=cf.id OR group_permissions.dataset_id=datasets.id)
  LEFT JOIN groups ON groups.id=group_permissions.group_id
  LEFT JOIN user_groups ON groups.id=user_groups.group_id
  GROUP BY sample_names
  ORDER BY &quot;sample_names&quot; desc NULLS LAST
  </code></pre>
  <p>Is there any other way I can improve the performance of this query ideally down to a just few seconds? I can rearrange it, use temp tables and indexes, whichever works best.</p>
  <p>Is it likely that increasing the RAM will help?</p>
  <p><strong>Updated explain analyze verbose:</strong></p>
  <pre class="lang-sql prettyprint-override"><code>                                                                                                                                                                                                                                                  QUERY PLAN                                                                                                                                                                                                                                                   
  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=1046905.99..1046907.17 rows=20 width=104) (actual time=15394.929..15409.256 rows=20 loops=1)
     Output: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))), (max(cfiles.last_modified)), (string_agg(DISTINCT (users.email)::text, ', '::text)), (string_agg(DISTINCT (groups.name)::text, ', '::text))
     -&gt;  GroupAggregate  (cost=1046905.99..1130738.74 rows=1426200 width=104) (actual time=15394.927..15409.228 rows=20 loops=1)
           Output: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))), max(cfiles.last_modified), string_agg(DISTINCT (users.email)::text, ', '::text), string_agg(DISTINCT (groups.name)::text, ', '::text)
           Group Key: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text)))
           -&gt;  Sort  (cost=1046905.99..1057967.74 rows=4424700 width=104) (actual time=15394.877..15400.483 rows=11067 loops=1)
                 Output: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))), cfiles.last_modified, users.email, groups.name
                 Sort Key: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))) DESC NULLS LAST
                 Sort Method: external merge  Disk: 163288kB
                 -&gt;  ProjectSet  (cost=41.40..74530.20 rows=4424700 width=104) (actual time=0.682..2933.628 rows=3399832 loops=1)
                       Output: jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text)), cfiles.last_modified, users.email, groups.name
                       -&gt;  Nested Loop Left Join  (cost=41.40..51964.23 rows=44247 width=526) (actual time=0.587..1442.326 rows=46031 loops=1)
                             Output: cfiles.property_values, cfiles.last_modified, users.email, groups.name
                             Join Filter: ((user_permissions.cfile_id = cfiles.id) OR (user_permissions.dataset_id = datasets.id))
                             Rows Removed by Join Filter: 2391425
                             -&gt;  Nested Loop Left Join  (cost=38.55..11694.81 rows=44247 width=510) (actual time=0.473..357.751 rows=46016 loops=1)
                                   Output: cfiles.property_values, cfiles.last_modified, cfiles.id, datasets.id, groups.name
                                   Join Filter: ((group_permissions.cfile_id = cfiles.id) OR (group_permissions.dataset_id = datasets.id))
                                   Rows Removed by Join Filter: 616311
                                   -&gt;  Hash Join  (cost=35.81..4721.54 rows=44247 width=478) (actual time=0.388..50.189 rows=44255 loops=1)
                                         Output: cfiles.property_values, cfiles.last_modified, cfiles.id, datasets.id
                                         Inner Unique: true
                                         Hash Cond: (cfiles.dataset_id = datasets.id)
                                         -&gt;  Seq Scan on public.cfiles  (cost=0.00..4568.77 rows=44247 width=478) (actual time=0.012..20.676 rows=44255 loops=1)
                                               Output: cfiles.id, cfiles.tid, cfiles.uuid, cfiles.dataset_id, cfiles.path, cfiles.name, cfiles.checksum, cfiles.size, cfiles.last_modified, cfiles.content_type, cfiles.locked, cfiles.property_values, cfiles.created_at, cfiles.updated_at
                                               Filter: (cfiles.tid = 5)
                                               Rows Removed by Filter: 1567
                                         -&gt;  Hash  (cost=28.14..28.14 rows=614 width=8) (actual time=0.363..0.363 rows=614 loops=1)
                                               Output: datasets.id
                                               Buckets: 1024  Batches: 1  Memory Usage: 32kB
                                               -&gt;  Seq Scan on public.datasets  (cost=0.00..28.14 rows=614 width=8) (actual time=0.004..0.194 rows=614 loops=1)
                                                     Output: datasets.id
                                   -&gt;  Materialize  (cost=2.74..4.39 rows=9 width=48) (actual time=0.000..0.003 rows=14 loops=44255)
                                         Output: group_permissions.cfile_id, group_permissions.dataset_id, groups.name
                                         -&gt;  Hash Right Join  (cost=2.74..4.35 rows=9 width=48) (actual time=0.049..0.071 rows=14 loops=1)
                                               Output: group_permissions.cfile_id, group_permissions.dataset_id, groups.name
                                               Hash Cond: (user_groups.group_id = groups.id)
                                               -&gt;  Seq Scan on public.user_groups  (cost=0.00..1.38 rows=38 width=8) (actual time=0.003..0.011 rows=38 loops=1)
                                                     Output: user_groups.id, user_groups.tid, user_groups.user_id, user_groups.group_id, user_groups.created_at, user_groups.updated_at
                                               -&gt;  Hash  (cost=2.62..2.62 rows=9 width=56) (actual time=0.039..0.039 rows=9 loops=1)
                                                     Output: group_permissions.cfile_id, group_permissions.dataset_id, groups.name, groups.id
                                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                     -&gt;  Hash Right Join  (cost=1.20..2.62 rows=9 width=56) (actual time=0.021..0.035 rows=9 loops=1)
                                                           Output: group_permissions.cfile_id, group_permissions.dataset_id, groups.name, groups.id
                                                           Hash Cond: (groups.id = group_permissions.group_id)
                                                           -&gt;  Seq Scan on public.groups  (cost=0.00..1.24 rows=24 width=40) (actual time=0.003..0.008 rows=24 loops=1)
                                                                 Output: groups.id, groups.tid, groups.name, groups.description, groups.default_uview, groups.created_at, groups.updated_at
                                                           -&gt;  Hash  (cost=1.09..1.09 rows=9 width=24) (actual time=0.010..0.010 rows=9 loops=1)
                                                                 Output: group_permissions.cfile_id, group_permissions.dataset_id, group_permissions.group_id
                                                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                 -&gt;  Seq Scan on public.group_permissions  (cost=0.00..1.09 rows=9 width=24) (actual time=0.003..0.006 rows=9 loops=1)
                                                                       Output: group_permissions.cfile_id, group_permissions.dataset_id, group_permissions.group_id
                             -&gt;  Materialize  (cost=2.85..4.78 rows=52 width=48) (actual time=0.000..0.010 rows=52 loops=46016)
                                   Output: user_permissions.cfile_id, user_permissions.dataset_id, users.email
                                   -&gt;  Hash Left Join  (cost=2.85..4.52 rows=52 width=48) (actual time=0.037..0.076 rows=52 loops=1)
                                         Output: user_permissions.cfile_id, user_permissions.dataset_id, users.email
                                         Inner Unique: true
                                         Hash Cond: (user_permissions.user_id = users.id)
                                         -&gt;  Seq Scan on public.user_permissions  (cost=0.00..1.52 rows=52 width=24) (actual time=0.003..0.014 rows=52 loops=1)
                                               Output: user_permissions.id, user_permissions.tid, user_permissions.user_id, user_permissions.dataset_id, user_permissions.cfile_id, user_permissions.read, user_permissions.share, user_permissions.write_meta, user_permissions.manage_files, user_permissions.delete_files, user_permissions.task_id, user_permissions.notified_at, user_permissions.first_downloaded_at, user_permissions.created_at, user_permissions.updated_at
                                         -&gt;  Hash  (cost=2.38..2.38 rows=38 width=40) (actual time=0.029..0.030 rows=38 loops=1)
                                               Output: users.email, users.id
                                               Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                               -&gt;  Seq Scan on public.users  (cost=0.00..2.38 rows=38 width=40) (actual time=0.004..0.016 rows=38 loops=1)
                                                     Output: users.email, users.id
   Planning Time: 0.918 ms
   Execution Time: 15442.689 ms
  (67 rows)
  
  Time: 15575.174 ms (00:15.575)
  </code></pre>
  <p><strong>Update 2</strong></p>
  <p>After increasing work_mem to 256mb, the cfiles without any joins query dropped from 12sec to 2sec, but the full query is still at 11 seconds - new plan below</p>
  <pre><code> Limit  (cost=1197580.62..1197582.26 rows=20 width=104) (actual time=11049.784..11057.060 rows=20 loops=1)
     Output: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))), (max(cfiles.last_modified)), (string_agg(DISTINCT (users.email)::text, ', '::text)), (string_agg(DISTINCT (groups.name)::text, ', '::text))
     -&gt;  GroupAggregate  (cost=1197580.62..1313691.62 rows=1423800 width=104) (actual time=11049.783..11057.056 rows=20 loops=1)
           Output: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))), max(cfiles.last_modified), string_agg(DISTINCT (users.email)::text, ', '::text), string_agg(DISTINCT (groups.name)::text, ', '::text)
           Group Key: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text)))
           -&gt;  Sort  (cost=1197580.62..1215107.62 rows=7010800 width=80) (actual time=11049.741..11051.064 rows=11067 loops=1)
                 Output: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))), cfiles.last_modified, users.email, groups.name
                 Sort Key: (jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text))) DESC NULLS LAST
                 Sort Method: external merge  Disk: 163248kB
                 -&gt;  ProjectSet  (cost=39.41..88894.93 rows=7010800 width=80) (actual time=0.314..1309.381 rows=3399832 loops=1)
                       Output: jsonb_array_elements_text((cfiles.property_values -&gt; 'Sample Names'::text)), cfiles.last_modified, users.email, groups.name
                       -&gt;  Hash Left Join  (cost=39.41..53139.85 rows=70108 width=502) (actual time=0.230..413.324 rows=46031 loops=1)
                             Output: cfiles.property_values, cfiles.last_modified, users.email, groups.name
                             Hash Cond: (groups.id = user_groups.group_id)
                             -&gt;  Nested Loop Left Join  (cost=37.55..51994.13 rows=44279 width=510) (actual time=0.218..405.437 rows=44535 loops=1)
                                   Output: cfiles.property_values, cfiles.last_modified, users.email, groups.name, groups.id
                                   Join Filter: ((user_permissions.cfile_id = cfiles.id) OR (user_permissions.dataset_id = datasets.id))
                                   Rows Removed by Join Filter: 2313897
                                   -&gt;  Nested Loop Left Join  (cost=34.70..11695.59 rows=44279 width=500) (actual time=0.166..99.664 rows=44520 loops=1)
                                         Output: cfiles.property_values, cfiles.last_modified, cfiles.id, datasets.id, groups.name, groups.id
                                         Join Filter: ((group_permissions.cfile_id = cfiles.id) OR (group_permissions.dataset_id = datasets.id))
                                         Rows Removed by Join Filter: 396532
                                         -&gt;  Hash Join  (cost=33.16..4718.97 rows=44279 width=478) (actual time=0.141..30.449 rows=44255 loops=1)
                                               Output: cfiles.property_values, cfiles.last_modified, cfiles.id, datasets.id
                                               Inner Unique: true
                                               Hash Cond: (cfiles.dataset_id = datasets.id)
                                               -&gt;  Seq Scan on public.cfiles  (cost=0.00..4568.77 rows=44279 width=478) (actual time=0.016..12.724 rows=44255 loops=1)
                                                     Output: cfiles.id, cfiles.tid, cfiles.uuid, cfiles.dataset_id, cfiles.path, cfiles.name, cfiles.checksum, cfiles.size, cfiles.last_modified, cfiles.content_type, cfiles.locked, cfiles.property_values, cfiles.created_at, cfiles.updated_at
                                                     Filter: (cfiles.tid = 5)
                                                     Rows Removed by Filter: 1567
                                               -&gt;  Hash  (cost=25.48..25.48 rows=614 width=8) (actual time=0.119..0.120 rows=614 loops=1)
                                                     Output: datasets.id
                                                     Buckets: 1024  Batches: 1  Memory Usage: 32kB
                                                     -&gt;  Index Only Scan using datasets_pkey on public.datasets  (cost=0.28..25.48 rows=614 width=8) (actual time=0.010..0.057 rows=614 loops=1)
                                                           Output: datasets.id
                                                           Heap Fetches: 0
                                         -&gt;  Materialize  (cost=1.54..2.70 rows=9 width=38) (actual time=0.000..0.001 rows=9 loops=44255)
                                               Output: group_permissions.cfile_id, group_permissions.dataset_id, groups.name, groups.id
                                               -&gt;  Hash Left Join  (cost=1.54..2.65 rows=9 width=38) (actual time=0.015..0.021 rows=9 loops=1)
                                                     Output: group_permissions.cfile_id, group_permissions.dataset_id, groups.name, groups.id
                                                     Inner Unique: true
                                                     Hash Cond: (group_permissions.group_id = groups.id)
                                                     -&gt;  Seq Scan on public.group_permissions  (cost=0.00..1.09 rows=9 width=24) (actual time=0.002..0.003 rows=9 loops=1)
                                                           Output: group_permissions.id, group_permissions.tid, group_permissions.group_id, group_permissions.dataset_id, group_permissions.cfile_id, group_permissions.read, group_permissions.share, group_permissions.write_meta, group_permissions.manage_files, group_permissions.delete_files, group_permissions.task_id, group_permissions.notified_at, group_permissions.first_downloaded_at, group_permissions.created_at, group_permissions.updated_at
                                                     -&gt;  Hash  (cost=1.24..1.24 rows=24 width=22) (actual time=0.009..0.010 rows=24 loops=1)
                                                           Output: groups.name, groups.id
                                                           Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                           -&gt;  Seq Scan on public.groups  (cost=0.00..1.24 rows=24 width=22) (actual time=0.003..0.006 rows=24 loops=1)
                                                                 Output: groups.name, groups.id
                                   -&gt;  Materialize  (cost=2.85..4.78 rows=52 width=42) (actual time=0.000..0.003 rows=52 loops=44520)
                                         Output: user_permissions.cfile_id, user_permissions.dataset_id, users.email
                                         -&gt;  Hash Left Join  (cost=2.85..4.52 rows=52 width=42) (actual time=0.021..0.036 rows=52 loops=1)
                                               Output: user_permissions.cfile_id, user_permissions.dataset_id, users.email
                                               Inner Unique: true
                                               Hash Cond: (user_permissions.user_id = users.id)
                                               -&gt;  Seq Scan on public.user_permissions  (cost=0.00..1.52 rows=52 width=24) (actual time=0.002..0.005 rows=52 loops=1)
                                                     Output: user_permissions.id, user_permissions.tid, user_permissions.user_id, user_permissions.dataset_id, user_permissions.cfile_id, user_permissions.read, user_permissions.share, user_permissions.write_meta, user_permissions.manage_files, user_permissions.delete_files, user_permissions.task_id, user_permissions.notified_at, user_permissions.first_downloaded_at, user_permissions.created_at, user_permissions.updated_at
                                               -&gt;  Hash  (cost=2.38..2.38 rows=38 width=34) (actual time=0.016..0.017 rows=38 loops=1)
                                                     Output: users.email, users.id
                                                     Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                                     -&gt;  Seq Scan on public.users  (cost=0.00..2.38 rows=38 width=34) (actual time=0.002..0.010 rows=38 loops=1)
                                                           Output: users.email, users.id
                             -&gt;  Hash  (cost=1.38..1.38 rows=38 width=8) (actual time=0.009..0.010 rows=38 loops=1)
                                   Output: user_groups.group_id
                                   Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                   -&gt;  Seq Scan on public.user_groups  (cost=0.00..1.38 rows=38 width=8) (actual time=0.002..0.005 rows=38 loops=1)
                                         Output: user_groups.group_id
   Planning Time: 0.990 ms
   Execution Time: 11081.013 ms
  (69 rows)
  
  Time: 11143.367 ms (00:11.143)
  </code></pre>