Быстрое определение местоположения по ip в postgresql

[AlexeyK]

А конкретные цифры «ускорений» есть?

[aymeshkov]

Действительно стоило привести. Добавил в статью.

[painEffect]

Кому интересно, вот статья на эту же тему, но гораздо более детальная, в том числе и с более подробными тестами нежели в данном топике.

[happybyte]

Конкретное ускорение получится, если разбить базу на 256 таблиц на основе первого октета адреса и делать селект из этих таблиц (а не из одной большой) выбирая таблицу по первому октету.

Типа так:

«select c from cdb%03lu where a <= %lu and %lu <= b;»,cdb_octet1(a),a,a

[DurRandir]

А почему именно GIST индекс, а не GIN? Таблица же создаётся один раз, а GIN будет быстрее работать на выборках.

[aymeshkov]

ip4r не поддерживает GIN индексы — только GIST.

[DurRandir]

Тогда понятно. Было бы интересно добавить в этот модуль ещё и GIN.

[aymeshkov]

Думаю разработчики модуля выбрали GIST потому что он более «универсальный» что ли. В этой конкретной задаче имело бы смысл использовать GIN, но в принципе он ощутимо понижает производительность вставки.

[Wott]

Есть уже хорошее решение для mysql

SELECT * FROM `ip_group_city` where `ip_start` <= INET_ATON('74.125.45.100') order by ip_start desc limit 1;

с простым индексом на ip_start

Думаю что на PG можно легко сделать подобное

[aymeshkov]

Да, хорошее решение.

[homm]

Да, я тоже хотел написать об этом же способе. Нужен только один индекс. Но у меня возвращался несколько другой результат:

SELECT IF( '{$ip}' <= `endIpNum`, `locId`, -1 ) AS `locId`
FROM `ip_blocks`
WHERE `startIpNum` <= '{$ip}'
ORDER BY `startIpNum`DESC
LIMIT 1

В результате, одна строка выбирается почти в любом случае, но locId содержит верный id только если айпишник действительно между startIpNum и endIpNum, а не ближайший к началу какого-то блока.

[Wott]

Совершенно верно, просто база должна иметь в том числе и начала для свободных блоков. Но мне кажется это не проблема сделать ( если вдруг в базе их нет ) резервные блоки offline

[homm]

Пугает меня такая зависимость от данных, плюс еще одно преобразование при импорте из IpGeoBase. Проверять надежнее.

[Wott]

На самом деле дополнительная проверка переноситься со времени обработки адреса на время обновления базы. По-моему это хорошо.

А правильную базу, в которой уже есть все блоки можно взять с ipinfodb.com/ip_database.php. Фришный Maxmind там уже есть.

[makis]

Maxmind предлагает альтернативное решение: модуль для Apache, который в связке с PHP-Api, судя по тестам, обладает просто фантастической производительностью. Причём база айпишников должна храниться в бинарном формате.

[aymeshkov]

Да, я про него упомянул в начале топика. Производительность дает на порядки большую, чем выборки из базы. Но все зависит от задачи, иногда просто удобнее использовать бд.

[homm]

иногда просто удобнее использовать бд

Собственно, вы же сами предлагаете какой-то сторонний модуль, который в 99 случаев из ста не установлен. В этом смысле ваш вариант не лучше модуля апача. Решение на чистой базе данных в UPD2.

[aymeshkov]

contrib для постгреса несколько отличается от модуля для апача и никак не меняет то, что задача решена средтвами БД. Борьба за «чистоту» базы от модулей в случае постгреса неправильный подход.
А так — да, решение в UPD2 действительно очень хорошее, жаль сам в свое время не допер. Гуглил не в ту сторону видимо:).

[alexxxst]

В мускуле вот так проверяю айпишники:

SELECT country_id FROM ip_pool WHERE (INET_ATON('127.0.0.1') & (POW(2, 32) — POW(2, 32 — SUBSTRING_INDEX(ip, '/', -1)))) = INET_ATON(SUBSTRING_INDEX(ip, '/', 1));

где 127.0.0.1 — адрес, который ищем, а сети прописаны внутри в базе в виде 127.0.0.1/24 например.
Эксплейн говорит: Using where; Using index — если ip = PK

[aymeshkov]

А что будете делать вот с такими данными?

| startip | endip | locationid |
| 192.168.0.1 | 192.168.0.5 | 1 |
| 192.168.0.5 | 192.168.0.18 | 2 |

В комментариях выше есть пример того, как правильно работать в mysql с geoip базами.

[alexxxst]

Для такого там выше есть пример. У меня все укладывалось в допустимые маски. Таких извращений не встречалось :)

[alrond]

Еще одна база от нас — WorldIP. Пока база для всех открыта только по странам.
Главное отличие от максминда и производных — наша база строится на основе данных с корневых маршрутизаторов, BGP таблиц и 45 точек наблюдений (сервера расположены по всему миру от аргентины до японии). А значит более точные данные.
Еще немного примеров здесь и здесь.. Таких сетей тысячи, и выявить из можно только такими методами…

А в постгресе можно использовать так или вот такие функции (в самом проекте WIPmania тоже используется постгрес):

create function inet_aton(inet) returns bigint language sql immutable as $f$ select $1 - inet '0.0.0.0' $f$;
create function inet_ntoa(bigint) returns inet language sql immutable as $f$ select $1 + inet '0.0.0.0' $f$;

И тогда запросы можно делать типа таких(отлично используется индекс по началу сети):

select inet_ntoa(startip), inet_ntoa(endip), country from worldip where startip<=inet_aton('199.51.126.33') order by startip desc limit 1

[KKS]

а разве два условия >= и <= в качестве замены between не решат проблему?
а преобразовывать ip-адрес в число лучше до запроса, средствами того_откуда_исполняется_запрос

[maxcom]

постгрессу все равно что between, что >= <=

[LighteR]

А почему не используются встроенные типы данных inet, cidr?

[aymeshkov]

Ими нельзя задать индексируемый блок ip-адресов (startip,endip) — в отличие от ip4r.

[DurRandir]

Кстати, случайно наткнулся на ещё одно интересное решение, основанное на встроенном типе box. Итоговый выигрыш — того же порядка, что и ip4r (GIN бы ему :))

postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE 19999999 BETWEEN startip AND endip;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on testip  (cost=0.00..19902.00 rows=200814 width=12) (actual time=3.457..434.218 rows=1 loops=1)
   Filter: ((19999999 >= startip) AND (19999999 <= endip))
 Total runtime: 434.299 ms
(3 rows)

Time: 435,865 ms

postgres=# CREATE INDEX ggg ON testip USING gist ((box(point(startip,startip),point(endip,endip))) box_ops);
CREATE INDEX
Time: 75530,079 ms
postgres=# EXPLAIN ANALYZE 
              SELECT * 
                 FROM testip 
                WHERE box(point(startip,startip),point(endip,endip)) @> box(point (19999999,19999999), point(19999999,19999999));
                                                                                                QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on testip  (cost=60.50..2550.14 rows=1000 width=12) (actual time=0.169..0.172 rows=1 loops=1)
   Recheck Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box)
   ->  Bitmap Index Scan on ggg  (cost=0.00..60.25 rows=1000 width=0) (actual time=0.152..0.152 rows=1 loops=1)
         Index Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box)
 Total runtime: 0.285 ms
(5 rows)

Time: 2,805 ms

© www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

[cod]

Вопрос: Зачем тут нужна база данных? Почему нельзя работать напрямую с геобазой посредством API?

[aymeshkov]

Выше я об этом писал. Конечно, было бы быстрее использовать например maxmind api, но существуют задачи, в которых выгоднее/нужнее использовать для этого БД. Например, если вообще вся эта катавасия происходит в бд, анализ логов и т.п.