Improving Performance Of Order By On Jsonb Cross Join With Inner Join Group By

I have two tables in PostgreSQL 12: a dataset has many cfiles and a cfile has one dataset SELECT * FROM datasets; id | name ----+---------- 1 | dataset1 2 | dataset2 SEL

Solution 1:

Let's create test data on postgresl 13 with 600 datasets, 45k cfiles.

BEGIN;

CREATETABLE cfiles (
 id SERIAL PRIMARY KEY, 
 dataset_id INTEGERNOTNULL,
 property_values jsonb NOTNULL);

INSERTINTO cfiles (dataset_id,property_values)
 SELECT1+(random()*600)::INTEGERAS did, 
   ('{"Sample Names": ["'||array_to_string(array_agg(DISTINCT prop),'","')||'"]}')::jsonb prop 
   FROM (
     SELECT1+(random()*45000)::INTEGERAS cid,
     'Samp'||(power(random(),2)*30)::INTEGERAS prop 
     FROM generate_series(1,45000*4)) foo 
   GROUPBY cid;

COMMIT;
CREATETABLE datasets ( id INTEGERPRIMARY KEY, name TEXT NOTNULL );
INSERTINTO datasets SELECT n, 'dataset'||n FROM (SELECTDISTINCT dataset_id n FROM cfiles) foo;
CREATE INDEX cfiles_dataset ON cfiles(dataset_id);
VACUUM ANALYZE cfiles;
VACUUM ANALYZE datasets;

Your original query is a lot faster here, but that's probably because postgres 13 is just smarter.

 Sort  (cost=114127.87..114129.37rows=601 width=46) (actual time=658.943..659.012rows=601 loops=1)
   Sort Key: datasets.name
   Sort Method: quicksort  Memory: 334kB
   ->  GroupAggregate  (cost=0.57..114100.13rows=601 width=46) (actual time=13.954..655.916rows=601 loops=1)
         Group Key: datasets.id
         ->  Nested Loop  (cost=0.57..92009.62rows=4416600 width=46) (actual time=13.373..360.991rows=163540 loops=1)
               ->MergeJoin  (cost=0.56..3677.61rows=44166 width=78) (actual time=13.350..113.567rows=44166 loops=1)
                     Merge Cond: (cfiles.dataset_id = datasets.id)
                     ->  Index Scan using cfiles_dataset on cfiles  (cost=0.29..3078.75rows=44166 width=68) (actual time=0.015..69.098rows=44166 loops=1)
                     ->  Index Scan using datasets_pkey on datasets  (cost=0.28..45.29rows=601 width=14) (actual time=0.024..0.580rows=601 loops=1)
               ->Function Scan on jsonb_array_elements_text sn  (cost=0.01..1.00rows=100 width=32) (actual time=0.003..0.004rows=4 loops=44166)
 Execution Time: 661.978 ms

This query reads a big table first (cfiles) and produces much less rows due to aggregation. Thus it will be faster to join with datasets after the number of rows to join is reduced, not before. Let's move that join. Also I got rid of the CROSS JOIN which is unnecessary, when there is a set-returning function in a SELECT postgres will do what you want for free.

SELECT dataset_id, d.name, sample_names FROM (
 SELECT dataset_id, string_agg(sn, '; ') as sample_names FROM (
  SELECTDISTINCT dataset_id,
   jsonb_array_elements_text(cfiles.property_values ->'Sample Names') AS sn
   FROM cfiles
   ) f GROUPBY dataset_id
  )g JOIN datasets d ON (d.id=g.dataset_id)
 ORDERBY d.name;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=536207.44..536207.94rows=200 width=46) (actual time=264.435..264.502rows=601 loops=1)
   Sort Key: d.name
   Sort Method: quicksort  Memory: 334kB
   ->  Hash Join  (cost=536188.20..536199.79rows=200 width=46) (actual time=261.404..261.784rows=601 loops=1)
         Hash Cond: (d.id = cfiles.dataset_id)
         ->  Seq Scan on datasets d  (cost=0.00..10.01rows=601 width=14) (actual time=0.025..0.124rows=601 loops=1)
         ->  Hash  (cost=536185.70..536185.70rows=200 width=36) (actual time=261.361..261.363rows=601 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 170kB
               ->  HashAggregate  (cost=536181.20..536183.70rows=200 width=36) (actual time=260.805..261.054rows=601 loops=1)
                     Group Key: cfiles.dataset_id
                     Batches: 1  Memory Usage: 1081kB
                     ->  HashAggregate  (cost=409982.82..507586.70rows=1906300 width=36) (actual time=244.419..253.094rows=18547 loops=1)
                           Group Key: cfiles.dataset_id, jsonb_array_elements_text((cfiles.property_values ->'Sample Names'::text))
                           Planned Partitions: 4  Batches: 1  Memory Usage: 13329kB
                           ->  ProjectSet  (cost=0.00..23530.32rows=4416600 width=36) (actual time=0.030..159.741rows=163540 loops=1)
                                 ->  Seq Scan on cfiles  (cost=0.00..1005.66rows=44166 width=68) (actual time=0.006..9.588rows=44166 loops=1)
 Planning Time: 0.247 ms
 Execution Time: 269.362 ms

That's better. But I see a LIMIT in your query, which means you're probably doing something like pagination. In this case it is only necessary to compute the whole query for the whole cfiles table and then throw away most of the results due to the LIMIT, IF the results of that big query can change whether a row from datasets is included in the final result or not. If that is the case, then rows in datasets which don't have corresponding cfiles will not appear in the final result, which means the contents of cfiles will affect pagination. Well, we can always cheat: to know if a row from datasets has to be included, all that is required is that ONE row from cfiles exists with that id...

So, in order to know which rows of datasets will be included in the final result, we can use one of these two queries:

SELECT id FROM datasets WHERE EXISTS( SELECT * FROM cfiles WHERE cfiles.dataset_id = datasets.id )
ORDERBY name LIMIT 20;

SELECT dataset_id FROM 
  (SELECT id AS dataset_id, name AS dataset_name FROM datasets ORDERBY dataset_name) f1
  WHERE EXISTS( SELECT * FROM cfiles WHERE cfiles.dataset_id = f1.dataset_id )
  ORDERBY dataset_name
  LIMIT 20;

Those take about 2-3 milliseconds. We can also cheat:

CREATE INDEX datasets_name_id ON datasets(name,id);

This brings it down to about 300 microseconds. So, now we got the list of dataset_id that will actually be used (and not thrown away) so we can use that to perform the big slow aggregation only on the rows that will actually be in the final result, which should save a large amount of unnecessary work...

WITH ds AS (SELECT id AS dataset_id, name AS dataset_name
 FROM datasets WHERE EXISTS( SELECT * FROM cfiles WHERE cfiles.dataset_id = datasets.id )
 ORDERBY name LIMIT 20)

SELECT dataset_id, dataset_name, sample_names FROM (
 SELECT dataset_id, string_agg(DISTINCT sn, '; ' ORDER BY sn) as sample_names FROM (SELECT dataset_id, 
   jsonb_array_elements_text(cfiles.property_values -> 'Sample Names') AS sn FROM ds JOIN cfiles USING (dataset_id)
  ) g GROUPBY dataset_id
  ) h JOIN ds USING (dataset_id)
 ORDERBY dataset_name;

This takes about 30ms, also I put the order by sample_name that I had forgotten before. It should work for your case. An important point is that query time no longer depends on the size of table cfiles, since it will only process the rows that are actually needed.

Please post results ;)

Solution 2:

DISTINCT in aggregate functions is not PostgreSQL's strong side.

Perhaps this will perform better:

SELECT id, name,
       string_agg(sample_names, '; ' ORDER BY sample_names) AS sample_namesFROM (SELECTDISTINCT datasets.id, datasets.name, sn.sample_names
      FROM cfiles
         CROSS JOIN jsonb_array_elements_text(
                       cfiles.property_values -> 'Sample Names'
                    ) AS sn(sample_names)
         JOIN datasets on cfiles.dataset_id = datasets.id
     ) AS q
GROUPBY id, name
ORDERBY name
LIMIT 20;