How to find similar records using multidimensional array?

Question

I have table that describes a movie release. This table consists of columns:

• movie_id int
• release_day int
• showtime_count int
• ticket_sold_count int

e.g. (1, 1, 100, 200) tuple describes movie ID 1, on the first day of the movie release when it had 100 showtimes and sold 200 tickets.

I have a movie that is 5 days into the release (e.g. (2,1,100,200), (2,2,150,300), (2,3,100,250), (2,4,150,300), (2,5,100,250)) and I want to find previous releases that have performed similarly up to this day into their release.

I know that I can calculate a distance between two points using <->. But how would I index and search the entire database for a movie where distance for days 1, 2, 3, 4 and 5 is the lowest?

Answer 1

Just focusing on the database and ignoring the statistics issues, and assuming the 5 days you use is a design decision and not just ephemeral example, and that we can ignore anything with an incomplete record, then you can do it like this:

Set up with random data:

create table movie as select x as movie_id, y as release_day, floor(random()*1000)::int as showtime_count, floor(random()*100000)::int as ticket_sold_count 
from generate_series(1,1000000) f(x), generate_series(1,6) g(y);

denormalize and index:

create materialized view release as 
    select movie_id, 
           cube(
              array_agg(showtime_count order by release_day)||
              array_agg(ticket_sold_count order by release_day)
            ) as datavector
    from movie where
        release_day <=5 
    group by movie_id 
    having count(release_day)=5 and 
           count(distinct release_day)=5;

create index on release using gist (datavector);

And use:

explain (analyze,buffers) select * from release order by datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)' limit 1;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..0.59 rows=1 width=44) (actual time=1.800..1.802 rows=1 loops=1)
   Buffers: shared hit=95
   ->  Index Scan using release_datavector_idx on release  (cost=0.41..176884.39 rows=999999 width=44) (actual time=1.798..1.798 rows=1 loops=1)
         Order By: (datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)'::cube)
         Buffers: shared hit=95
 Planning Time: 0.091 ms
 Execution Time: 1.841 ms