Re: [pgsql-ru-general] Создание GIST индексам для сравнения картинок (intarray)

Привет всем,

2011/2/6 Oleg Bartunov <oleg@sai.msu.su>:
> Можно попробовать использовать GIN индекс по полю diff_array, который
> ускоряет операцию &&

А может быть имеет смысл использовать tsearch, построив gin/gist
индекс по to_tsvector(array_to_string(diff_array, ' ')) и используя
ts_rank() в ORDR BY?

Тогда запрос будет выглядеть как-то так:

SELECT ..., ts_rank(to_tsvector(array_to_string(diff_array, ' ')),
query) AS rank
FROM some_table, to_tsquery('1 2 3 4 5') AS query
WHERE to_tsvector(array_to_string(diff_array, ' ')) @@ query
ORDER BY rank
LIMIT 10;

А фильтровать >60% уже на клиенте обрабатывая всего 10 результатов.

Не будет ли это быстрее работать?

>
> CREATE OR REPLACE FUNCTION intersection(anyarray, anyarray) RETURNS anyarray
> as $$
> SELECT ARRAY(
>   SELECT $1[i]
>   FROM generate_series( array_lower($1, 1), array_upper($1, 1) ) WHERE
> ARRAY[$1[i]] && $2 ); $$ language sql;
>
>
> Олег
>
> On Sun, 6 Feb 2011, leopard_ne@inbox.ru wrote:
>
>> Добрый день. Прошу совета у сообщества.
>> Есть простой метод сравнения картинок -
>>
>> http://leopard.in.ua/2010/12/09/bystroe-sravnenie-izobrazhenij-s-pomoshhyu-rubyphp-i-postgresql/
>>
>> В таблицу записывается в поле типа integer[] масив с числами (пусть поле
>> будет "diff_array" в таблице "some_table"). Далее происходит такой метод
>> поиска похожих картинок (пусть есть массив картинки "{1,3,4,5,...}"
>> размером 225, как в статье)
>>
>> Select some_table.*, round((icount(some_table.diff_array::int[] &
>> '{1,3,4,5,...}'::int[])::numeric / (225)::numeric) * 100, 2) as
>> simillar_percentes FROM some_table WHERE
>> round((icount(some_table.diff_array::int[] &
>> '{1,3,4,5,...}'::int[])::numeric / (225)::numeric) * 100, 2) >= 60 AND
>> id != img_id ORDER BY round((icount(some_table.diff_array::int[] &
>> '{1,3,4,5,...}'::int[])::numeric / (225)::numeric) * 100, 2) DESC LIMIT 10
>>
>> Проблемный кусок, который используется 3 раза тут в запросе:
>>
>> round((icount(some_table.diff_array::int[] &
>> '{1,3,4,5,...}'::int[])::numeric / (225)::numeric) * 100, 2)
>>
>> Хотелось бы как то по такому условию создать индекс, поскольку скорость
>> падает (индекс gist__intbig_ops добавлял, скорость все равно не
>> увеличивается).
>> Для простоты вычисления размера массива (чтобы не задать 225 = 15*15
>> матрица), можно сделать так функцию:
>>
>> round((icount(some_table.diff_array::int[] &
>> '{1,3,4,5,...}'::int[])::numeric /
>> ((icount(some_table.diff_array::int[]) + icount('{1,3,4,5,...}'::int[]))
>> / 2)::numeric) * 100, 2)
>>
>> Документацию
>> http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html
>> <http://www.sai.msu.su/%7Emegera/postgres/talks/gist_tutorial.html> я
>> прочитал, но не работаю на C, и поэтому, возникло еще больше вопросов,
>> как все таки мне можно создать индекс на подобное условие. Буду
>> благодарен за любые советы или помощь.
>>
>>
>
>        Regards,
>                Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
> --
> Sent via pgsql-ru-general mailing list (pgsql-ru-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-ru-general
>



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp