I'm developing a mechanism for a wallpaper gallery that would allow to search for visually similar images. Each wallpaper in the database have a "simdata" property that is an array of normalized pixels. My idea is to compute visual similarity between two wallpapers by calculating mean deviation between corresponding elements of their "simdata" vectors. Here's the solution in Postgres I came up with:
select "w".*, (
select 100 - 100 * avg(abs(simdata[i] - ('{1,2,3}'::integer[])[i])) / 255
from (
select generate_subscripts('{1,2,3}'::integer[], 1) as i
) as "t"
) as "similarity"
from "wallpapers" as "w"
order by "similarity" desc
limit 24
Where '{1,2,3}' is just a placeholder for a huge array of 1024 elements. It works, but doesn't look like a good solution in terms performance. Is it possible to improve it?
UPD. Came up with a slightly better query:
select "w".id, (
select 100 - 100 * avg(abs(simdata[i] - c)) / 255
from unnest('{1,2,3}'::integer[]) with ordinality tmp(c, i)
) as "similarity"
from "wallpapers" as "w"
order by "similarity" desc
limit 24