Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select setseed(0.1);
create table movie(
  id serial primary key,
  name text);
create table movie_similarity_eav(
  movie_id int references movie(id),
  similar_movie_id int references movie(id),
  similarity float,
  primary key (movie_id,similar_movie_id));

setseed

SELECT 1

CREATE TABLE

CREATE TABLE

insert into movie select n,gen_random_uuid() from generate_series(0,420)_(n);
insert into movie_similarity_eav
select m1.id,
       m2.id,
       random() 
from movie m1,
     movie m2;

INSERT 0 421

INSERT 0 177241

update movie_similarity_eav set similarity=1 where movie_id=similar_movie_id;
create index on movie_similarity_eav(movie_id,similar_movie_id,similarity);

UPDATE 421

CREATE INDEX

vacuum full analyze movie_similarity_eav;

VACUUM

reindex table movie_similarity_eav;

REINDEX

analyze movie_similarity_eav;

ANALYZE

select * from movie_similarity_eav order by 1,2 limit 5;

movie_id
similar_movie_id
similarity

0
0
1

0
1
0.6402839231277071

0
2
0.80326410702165

0
3
0.931589426258701

0
4
0.6259215008930064

SELECT 5

create table movie_similarity_aggregated(
  movie_id int references movie(id) primary key,
  similarity_matrix jsonb);
insert into movie_similarity_aggregated
select movie_id
      ,jsonb_build_object( 'similar_movies'
                          ,jsonb_object_agg( similar_movie_id
                                            ,similarity) )
from movie_similarity_eav
group by 1;

CREATE TABLE

INSERT 0 421

--type `json` used to preserve key order in an object
explain analyze verbose
select json_build_object( 'movie_id',movie_id,'similar_movies'
                         ,json_object_agg( similar_movie_id
                                          ,similarity order by score) )
from (select movie_id
            ,j.key AS similar_movie_id
            ,j.value::float AS similarity
            ,row_number()over(partition by movie_id order by j.value::float desc) score
      from movie_similarity_aggregated
          ,jsonb_each(similarity_matrix->'similar_movies')AS j)_
where score<=10
group by movie_id;

QUERY PLAN

GroupAggregate  (cost=91.97..19446.32 rows=200 width=36) (actual time=3.236..239.118 rows=421 loops=1)

  Output: json_build_object('movie_id', movie_similarity_aggregated.movie_id, 'similar_movies', json_object_agg(j.key, ((j.value)::double precision) ORDER BY (row_number() OVER (?)))), movie_similarity_aggregated.movie_id

  Group Key: movie_similarity_aggregated.movie_id

  ->  Incremental Sort  (cost=91.97..18808.32 rows=127000 width=52) (actual time=3.209..236.302 rows=4210 loops=1)

        Output: movie_similarity_aggregated.movie_id, j.key, ((j.value)::double precision), (row_number() OVER (?))

        Sort Key: movie_similarity_aggregated.movie_id, (row_number() OVER (?))

        Presorted Key: movie_similarity_aggregated.movie_id

        Full-sort Groups: 106  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB

        ->  WindowAgg  (cost=5.91..11304.58 rows=127000 width=52) (actual time=0.978..234.914 rows=4210 loops=1)

              Output: movie_similarity_aggregated.movie_id, j.key, ((j.value)::double precision), row_number() OVER (?)

              Run Condition: (row_number() OVER (?) <= 10)

              ->  Incremental Sort  (cost=5.91..8764.58 rows=127000 width=44) (actual time=0.961..221.860 rows=177241 loops=1)

                    Output: movie_similarity_aggregated.movie_id, ((j.value)::double precision), j.key

                    Sort Key: movie_similarity_aggregated.movie_id, ((j.value)::double precision) DESC

                    Presorted Key: movie_similarity_aggregated.movie_id

                    Full-sort Groups: 421  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

                    Pre-sorted Groups: 421  Sort Method: quicksort  Average Memory: 44kB  Peak Memory: 44kB

                    ->  Nested Loop  (cost=0.28..2932.83 rows=127000 width=44) (actual time=0.428..165.146 rows=177241 loops=1)

                          Output: movie_similarity_aggregated.movie_id, (j.value)::double precision, j.key

                          ->  Index Scan using movie_similarity_aggregated_pkey on public.movie_similarity_aggregated  (cost=0.28..75.33 rows=1270 width=36) (actual time=0.009..0.795 rows=421 loops=1)

                                Output: movie_similarity_aggregated.movie_id, movie_similarity_aggregated.similarity_matrix

                          ->  Function Scan on pg_catalog.jsonb_each j  (cost=0.01..1.00 rows=100 width=64) (actual time=0.137..0.165 rows=421 loops=421)

                                Output: j.key, j.value

                                Function Call: jsonb_each((movie_similarity_aggregated.similarity_matrix -> 'similar_movies'::text))

Planning Time: 0.425 ms

Execution Time: 239.217 ms

EXPLAIN

--solidify the order by nesting key-value pairs as objects in an array
explain analyze verbose
select jsonb_build_object( 'movie_id',movie_id,'similar_movies'
                          ,jsonb_agg(jsonb_build_object( similar_movie_id
                                                        ,similarity) 
                                     order by score))
from (select movie_id
            ,j.key AS similar_movie_id
            ,j.value::float AS similarity
            ,row_number()over(partition by movie_id order by j.value::float desc) score
      from movie_similarity_aggregated
          ,jsonb_each(similarity_matrix->'similar_movies')AS j)_
where score<=10
group by movie_id;

QUERY PLAN

GroupAggregate  (cost=91.97..19763.82 rows=200 width=36) (actual time=2.952..246.766 rows=421 loops=1)

  Output: jsonb_build_object('movie_id', movie_similarity_aggregated.movie_id, 'similar_movies', jsonb_agg(jsonb_build_object(j.key, ((j.value)::double precision)) ORDER BY (row_number() OVER (?)))), movie_similarity_aggregated.movie_id

  Group Key: movie_similarity_aggregated.movie_id

  ->  Incremental Sort  (cost=91.97..18808.32 rows=127000 width=52) (actual time=2.917..237.221 rows=4210 loops=1)

        Output: movie_similarity_aggregated.movie_id, j.key, ((j.value)::double precision), (row_number() OVER (?))

        Sort Key: movie_similarity_aggregated.movie_id, (row_number() OVER (?))

        Presorted Key: movie_similarity_aggregated.movie_id

        Full-sort Groups: 106  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB

        ->  WindowAgg  (cost=5.91..11304.58 rows=127000 width=52) (actual time=0.692..235.812 rows=4210 loops=1)

              Output: movie_similarity_aggregated.movie_id, j.key, ((j.value)::double precision), row_number() OVER (?)

              Run Condition: (row_number() OVER (?) <= 10)

              ->  Incremental Sort  (cost=5.91..8764.58 rows=127000 width=44) (actual time=0.689..222.863 rows=177241 loops=1)

                    Output: movie_similarity_aggregated.movie_id, ((j.value)::double precision), j.key

                    Sort Key: movie_similarity_aggregated.movie_id, ((j.value)::double precision) DESC

                    Presorted Key: movie_similarity_aggregated.movie_id

                    Full-sort Groups: 421  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

                    Pre-sorted Groups: 421  Sort Method: quicksort  Average Memory: 44kB  Peak Memory: 44kB

                    ->  Nested Loop  (cost=0.28..2932.83 rows=127000 width=44) (actual time=0.155..166.149 rows=177241 loops=1)

                          Output: movie_similarity_aggregated.movie_id, (j.value)::double precision, j.key

                          ->  Index Scan using movie_similarity_aggregated_pkey on public.movie_similarity_aggregated  (cost=0.28..75.33 rows=1270 width=36) (actual time=0.009..0.842 rows=421 loops=1)

                                Output: movie_similarity_aggregated.movie_id, movie_similarity_aggregated.similarity_matrix

                          ->  Function Scan on pg_catalog.jsonb_each j  (cost=0.01..1.00 rows=100 width=64) (actual time=0.137..0.167 rows=421 loops=421)

                                Output: j.key, j.value

                                Function Call: jsonb_each((movie_similarity_aggregated.similarity_matrix -> 'similar_movies'::text))

Planning Time: 0.164 ms

Execution Time: 246.857 ms

EXPLAIN

explain analyze verbose
select jsonb_build_object( 'movie_id',movie_id,'similar_movies'
                          ,jsonb_agg(jsonb_build_object( similar_movie_id
                                                        ,similarity) 
                                     order by score))
from (select *,row_number()over(partition by movie_id order by similarity desc) AS score
      from movie_similarity_eav )_
where score<=10
group by movie_id;

QUERY PLAN

GroupAggregate  (cost=100.58..31185.99 rows=421 width=36) (actual time=1.235..111.626 rows=421 loops=1)

  Output: jsonb_build_object('movie_id', movie_similarity_eav.movie_id, 'similar_movies', jsonb_agg(jsonb_build_object(movie_similarity_eav.similar_movie_id, movie_similarity_eav.similarity) ORDER BY (row_number() OVER (?)))), movie_similarity_eav.movie_id

  Group Key: movie_similarity_eav.movie_id

  ->  Incremental Sort  (cost=100.58..29850.37 rows=177241 width=24) (actual time=1.203..102.477 rows=4210 loops=1)

        Output: movie_similarity_eav.movie_id, movie_similarity_eav.similar_movie_id, movie_similarity_eav.similarity, (row_number() OVER (?))

        Sort Key: movie_similarity_eav.movie_id, (row_number() OVER (?))

        Presorted Key: movie_similarity_eav.movie_id

        Full-sort Groups: 106  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB

        ->  WindowAgg  (cost=35.04..19900.79 rows=177241 width=24) (actual time=0.221..101.367 rows=4210 loops=1)

              Output: movie_similarity_eav.movie_id, movie_similarity_eav.similar_movie_id, movie_similarity_eav.similarity, row_number() OVER (?)

              Run Condition: (row_number() OVER (?) <= 10)

              ->  Incremental Sort  (cost=35.04..16799.07 rows=177241 width=16) (actual time=0.218..88.527 rows=177241 loops=1)

                    Output: movie_similarity_eav.movie_id, movie_similarity_eav.similarity, movie_similarity_eav.similar_movie_id

                    Sort Key: movie_similarity_eav.movie_id, movie_similarity_eav.similarity DESC

                    Presorted Key: movie_similarity_eav.movie_id

                    Full-sort Groups: 421  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

                    Pre-sorted Groups: 421  Sort Method: quicksort  Average Memory: 44kB  Peak Memory: 44kB

                    ->  Index Scan using movie_similarity_eav_pkey on public.movie_similarity_eav  (cost=0.42..6849.49 rows=177241 width=16) (actual time=0.014..36.296 rows=177241 loops=1)

                          Output: movie_similarity_eav.movie_id, movie_similarity_eav.similarity, movie_similarity_eav.similar_movie_id

Planning Time: 0.139 ms

Execution Time: 111.706 ms

EXPLAIN
movie_id	similar_movie_id	similarity
0	0	1
0	1	0.6402839231277071
0	2	0.80326410702165
0	3	0.931589426258701
0	4	0.6259215008930064