Интересный вопрос. У меня есть несколько карточек другого типа, с которыми я экспериментировал. Создалось впечатление что нужно что-то для совместимости с GPG. Т.к. ни одна из них через gpg не распознавалась.
Итак… Сделал более правильную базу. В таблице 1037350 строк — база КЛАДР до уровня улиц.
Проверяю 3 запроса:
1. Запрос для нахождения пути к элементу по id
2. Проверка вхождения одного элемента в другой по 2-м id
3. Нахождение всех детей данного элемента
Без индекса:
1. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.tree @> w2.tree order by w1.tree;
QUERY PLAN
— Sort (cost=42339.34..42341.85 rows=1002 width=123) (actual time=1563.414..1563.414 rows=4 loops=1)
Sort Key: w1.tree
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..42289.40 rows=1002 width=123) (actual time=278.245..1563.387 rows=4 loops=1)
Join Filter: (w1.tree @> w2.tree)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.020..0.023 rows=1 loops=1)
Index Cond: (id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
-> Seq Scan on world w1 (cost=0.00..29759.25 rows=1001725 width=123) (actual time=0.034..710.075 rows=1037350 loops=1)
Total runtime: 1563.470 ms
(9 rows)
2. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree @> w2.tree;
QUERY PLAN
— Nested Loop (cost=0.00..17.19 rows=1 width=123) (actual time=0.043..0.048 rows=1 loops=1)
Join Filter: (w1.tree @> w2.tree)
-> Index Scan using world_pkey on world w1 (cost=0.00..8.59 rows=1 width=123) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.009..0.011 rows=1 loops=1)
Index Cond: (w2.id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
Total runtime: 0.089 ms
(7 rows)
3. # explain analyze select w1.* from world w1, world w2 where w2.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree <@ w2.tree;
QUERY PLAN
— Nested Loop (cost=0.00..42289.40 rows=1002 width=123) (actual time=1.099..1585.661 rows=11136 loops=1)
Join Filter: (w1.tree <@ w2.tree)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.021..0.024 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Seq Scan on world w1 (cost=0.00..29759.25 rows=1001725 width=123) (actual time=0.042..711.731 rows=1037350 loops=1)
Total runtime: 1591.608 ms
(6 rows)
Те-же запросы с индексом gist:
1. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.tree @> w2.tree order by w1.tree;
QUERY PLAN
— Sort (cost=3550.78..3553.37 rows=1037 width=123) (actual time=0.803..0.806 rows=4 loops=1)
Sort Key: w1.tree
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=109.27..3498.83 rows=1037 width=123) (actual time=0.732..0.784 rows=4 loops=1)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.078..0.080 rows=1 loops=1)
Index Cond: (id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
-> Bitmap Heap Scan on world w1 (cost=109.27..3477.28 rows=1037 width=123) (actual time=0.645..0.689 rows=4 loops=1)
Recheck Cond: (w1.tree @> w2.tree)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.01 rows=1037 width=0) (actual time=0.639..0.639 rows=4 loops=1)
Index Cond: (w1.tree @> w2.tree)
Total runtime: 0.865 ms
(11 rows)
2. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree @> w2.tree;
QUERY PLAN
— Nested Loop (cost=0.00..17.19 rows=1 width=123) (actual time=0.041..0.046 rows=1 loops=1)
Join Filter: (w1.tree @> w2.tree)
-> Index Scan using world_pkey on world w1 (cost=0.00..8.59 rows=1 width=123) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (w2.id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
Total runtime: 0.090 ms
(7 rows)
3. # explain analyze select w1.* from world w1, world w2 where w2.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree <@ w2.tree;
QUERY PLAN
— Nested Loop (cost=109.27..3498.83 rows=1037 width=123) (actual time=4.184..26.625 rows=11136 loops=1)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.020..0.023 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Bitmap Heap Scan on world w1 (cost=109.27..3477.28 rows=1037 width=123) (actual time=4.155..12.353 rows=11136 loops=1)
Recheck Cond: (w1.tree <@ w2.tree)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.01 rows=1037 width=0) (actual time=4.083..4.083 rows=11136 loops=1)
Index Cond: (w1.tree <@ w2.tree)
Total runtime: 33.261 ms
(8 rows)
В общем, по этим результатам представляется достаточно очевидным, что использование gist индекса на поле типа ltree существенно помогает запросам с ним.
# explain analyze select * from «world» where «tree» ~ '*{1}';
QUERY PLAN
— Bitmap Heap Scan on world (cost=109.28..3479.25 rows=1037 width=123) (actual time=446.029..446.031 rows=1 loops=1)
Recheck Cond: (tree ~ '*{1}'::lquery)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.02 rows=1037 width=0) (actual time=445.999..445.999 rows=1 loops=1)
Index Cond: (tree ~ '*{1}'::lquery)
Total runtime: 446.072 ms
(5 rows)
Вот БЕЗ индекса world_gist_idx (удален):
# explain analyze select * from «world» where «tree» ~ '*{1}';
QUERY PLAN
— Seq Scan on world (cost=0.00..32672.88 rows=1037 width=123) (actual time=178.695..486.991 rows=1 loops=1)
Filter: (tree ~ '*{1}'::lquery)
Total runtime: 487.025 ms
(3 rows)
Вообще, не очень показательно.
Я заметил что неправильно заполнил базу. Сейчас запущу заново ее формирование и завтра смогу сделать тесты с индексами и без них на нормальной базе и более сложных запросах.
rKey и lKey в данном случае вполне переносимы на другие субд, т.к. это обычные числовые поля. Комбинация parentid, rKey и lKey позволяет делать очень быстрые относительно сложные выборки, которые при наличии только parentid делались-бы и медленнее и неудобнее.
Заполнил базу данными из kladr (с детализацией до улиц — в районе 1 млн. записей) сделанными на основе nested sets. Поле tree сделал со значениями l_r из netsed sets (id большие — типа uuid) и проиндексировал (может и не нужно было — не знаю).
Во первых, индекс по этому полю занял 353 Мб при том что сама таблица в районе 143 Мб.
По первым-же тестам даже на глаз видно что вариант ltree работает существенно медленнее. Хотя, этого следовало ожидать. В nested sets запросы идут по индексируемым числовым полям. Сомневаюсь что быстрее возможно.
Однако, следует отметить что величины времен запросов в варианте ltree вполне приемлимы. Для себя сделал вывод что этот метод вполне заслуживает свое место в разработке. Например в вариантах, когда требуется частное изменение или добавление элементов в дереве.
Это, в общем-то очевидно. Меня больше интересует сравнение скорости на выборке. У меня есть база KLADR в nested sets. Сейчас ее перегоняю в базу с ltree. Посмотрим, что получится.
Плюс автору однозначно. Даже не предполагал о таких продвинутых возможностях postgresql. Относительно предмета разговора, попробую потестировать скорость данного варианта по сравнению с nested sets. В данном обзоре этого сильно не хватает.
Есть, конечно, небольшое опасение что развитие открытого варианта может затормозиться. Но, честно говоря, я в это не верю. Более реально что коммерческий вариант будет развиваться быстрее именно за счет открытого. Сообщество, в любом случае сделает больше чем один Игорь.
А вообще, очень раз да него. Теперь главное что-бы тепличные бытовые условия не вызвали застой в разработке. :)
Спасибо! Очень интересно. Когда был в США, все время обращал внимание на все эти прибамбасы, но не видел детальной информации о том что это и зачем нужно. Вообще из того что там видел считаю систему управления движением одной из самых хороших вещей в США.
По своим предыдущим задачам могу скачать что востребовано будет API изменения веса, например, выдачи IP адресов для домена в round-robin. Кстати, если будете ориентироваться на то, что клиент захочет вместе с вами разрабатывать и отлаживать ваш софт, будте готовы к тому, что он и скидку потребует в лучшем случае. Т.к. он за вас сделает часть вашей работы — постановку задачи. В худшем случае просто не будет связываться.
И подумайте, все-таки над тем, что API на самом деле может стать той «изюминкой» которая может помочь вам продвинуться. Низкий TTL — это приятная, но обычная фишка при установке стандартного DNS сервера самостоятельно. А вот API клиент так просто не получит.
Т.е. низкий TTL — это главная фишка этого хостинга? В общем-то штука нужная. Однако, если у вас настолько критичное приложение что оно чувствительно к таким вещам, думаю вам надо ставить свой DNS сервер. Хотя, наличие варинатов, это однозначно хорошо. Тем не менее, все-равно не уверен что такая цена уместна.
А вот если будет API (когда оно будет) для быстрого динамического изменения параметров DNS — вот это будет то, что оправдает текущую цену. Если, конечно, она не повыситься к тому времени.
Ну это смотря с какой стороны смотреть. В данном случае «динозавры» — положительная характеристика. Они стабильны, давно работают и поддержка клиентов давно отработано. Вот что может предложить этот сервис что-бы все это перевесить? Я не издеваюсь, я действительно хочу узнать. Может я просто чего-то не понимаю в современном DNS хостинге?
Не уверен что у вас приемлимая цена для только-что открытого хостинга. Я ориентируюсь по nic.ru. У них стоимость primary-DNS 620 рублей в год. Это в районе $1.8 в месяц. За счет каких сервисов или особенностей у вас стоимость выше чем у них?
Если не смогли отследить кто руководил командой, неужели настолько сложно отследить движение денег через платный номер? Его-же, наверное, кто-то регистрировал, заключал договор… Потом деньги перечислялись на определенные счета. Это-же все можно отследить, по идее.
Проверяю 3 запроса:
1. Запрос для нахождения пути к элементу по id
2. Проверка вхождения одного элемента в другой по 2-м id
3. Нахождение всех детей данного элемента
Без индекса:
1. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.tree @> w2.tree order by w1.tree;
QUERY PLAN
— Sort (cost=42339.34..42341.85 rows=1002 width=123) (actual time=1563.414..1563.414 rows=4 loops=1)
Sort Key: w1.tree
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..42289.40 rows=1002 width=123) (actual time=278.245..1563.387 rows=4 loops=1)
Join Filter: (w1.tree @> w2.tree)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.020..0.023 rows=1 loops=1)
Index Cond: (id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
-> Seq Scan on world w1 (cost=0.00..29759.25 rows=1001725 width=123) (actual time=0.034..710.075 rows=1037350 loops=1)
Total runtime: 1563.470 ms
(9 rows)
2. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree @> w2.tree;
QUERY PLAN
— Nested Loop (cost=0.00..17.19 rows=1 width=123) (actual time=0.043..0.048 rows=1 loops=1)
Join Filter: (w1.tree @> w2.tree)
-> Index Scan using world_pkey on world w1 (cost=0.00..8.59 rows=1 width=123) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.009..0.011 rows=1 loops=1)
Index Cond: (w2.id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
Total runtime: 0.089 ms
(7 rows)
3. # explain analyze select w1.* from world w1, world w2 where w2.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree <@ w2.tree;
QUERY PLAN
— Nested Loop (cost=0.00..42289.40 rows=1002 width=123) (actual time=1.099..1585.661 rows=11136 loops=1)
Join Filter: (w1.tree <@ w2.tree)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.021..0.024 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Seq Scan on world w1 (cost=0.00..29759.25 rows=1001725 width=123) (actual time=0.042..711.731 rows=1037350 loops=1)
Total runtime: 1591.608 ms
(6 rows)
Те-же запросы с индексом gist:
1. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.tree @> w2.tree order by w1.tree;
QUERY PLAN
— Sort (cost=3550.78..3553.37 rows=1037 width=123) (actual time=0.803..0.806 rows=4 loops=1)
Sort Key: w1.tree
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=109.27..3498.83 rows=1037 width=123) (actual time=0.732..0.784 rows=4 loops=1)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.078..0.080 rows=1 loops=1)
Index Cond: (id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
-> Bitmap Heap Scan on world w1 (cost=109.27..3477.28 rows=1037 width=123) (actual time=0.645..0.689 rows=4 loops=1)
Recheck Cond: (w1.tree @> w2.tree)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.01 rows=1037 width=0) (actual time=0.639..0.639 rows=4 loops=1)
Index Cond: (w1.tree @> w2.tree)
Total runtime: 0.865 ms
(11 rows)
2. # explain analyze select w1.* from world w1, world w2 where w2.id='12b4603e-c0d4-11e0-a74e-9039b8fc67c8' and w1.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree @> w2.tree;
QUERY PLAN
— Nested Loop (cost=0.00..17.19 rows=1 width=123) (actual time=0.041..0.046 rows=1 loops=1)
Join Filter: (w1.tree @> w2.tree)
-> Index Scan using world_pkey on world w1 (cost=0.00..8.59 rows=1 width=123) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (w2.id = '12b4603e-c0d4-11e0-a74e-9039b8fc67c8'::uuid)
Total runtime: 0.090 ms
(7 rows)
3. # explain analyze select w1.* from world w1, world w2 where w2.id='05bb280e-c0d4-11e0-bd30-c89f5da7af3e' and w1.tree <@ w2.tree;
QUERY PLAN
— Nested Loop (cost=109.27..3498.83 rows=1037 width=123) (actual time=4.184..26.625 rows=11136 loops=1)
-> Index Scan using world_pkey on world w2 (cost=0.00..8.59 rows=1 width=83) (actual time=0.020..0.023 rows=1 loops=1)
Index Cond: (id = '05bb280e-c0d4-11e0-bd30-c89f5da7af3e'::uuid)
-> Bitmap Heap Scan on world w1 (cost=109.27..3477.28 rows=1037 width=123) (actual time=4.155..12.353 rows=11136 loops=1)
Recheck Cond: (w1.tree <@ w2.tree)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.01 rows=1037 width=0) (actual time=4.083..4.083 rows=11136 loops=1)
Index Cond: (w1.tree <@ w2.tree)
Total runtime: 33.261 ms
(8 rows)
В общем, по этим результатам представляется достаточно очевидным, что использование gist индекса на поле типа ltree существенно помогает запросам с ним.
# explain analyze select * from «world» where «tree» ~ '*{1}';
QUERY PLAN
— Bitmap Heap Scan on world (cost=109.28..3479.25 rows=1037 width=123) (actual time=446.029..446.031 rows=1 loops=1)
Recheck Cond: (tree ~ '*{1}'::lquery)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.02 rows=1037 width=0) (actual time=445.999..445.999 rows=1 loops=1)
Index Cond: (tree ~ '*{1}'::lquery)
Total runtime: 446.072 ms
(5 rows)
Вот БЕЗ индекса world_gist_idx (удален):
# explain analyze select * from «world» where «tree» ~ '*{1}';
QUERY PLAN
— Seq Scan on world (cost=0.00..32672.88 rows=1037 width=123) (actual time=178.695..486.991 rows=1 loops=1)
Filter: (tree ~ '*{1}'::lquery)
Total runtime: 487.025 ms
(3 rows)
Вообще, не очень показательно.
Я заметил что неправильно заполнил базу. Сейчас запущу заново ее формирование и завтра смогу сделать тесты с индексами и без них на нормальной базе и более сложных запросах.
# explain select * from «world» where «tree» ~ '*{1}';
QUERY PLAN
— Bitmap Heap Scan on world (cost=109.28..3479.25 rows=1037 width=123)
Recheck Cond: (tree ~ '*{1}'::lquery)
-> Bitmap Index Scan on world_gist_idx (cost=0.00..109.02 rows=1037 width=0)
Index Cond: (tree ~ '*{1}'::lquery)
(4 rows)
# explain select * from «world» where «tree» ~ '*{1}';
QUERY PLAN
— Seq Scan on world (cost=0.00..32690.29 rows=1039 width=123)
Filter: (tree ~ '*{1}'::lquery)
(2 rows)
Во первых, индекс по этому полю занял 353 Мб при том что сама таблица в районе 143 Мб.
По первым-же тестам даже на глаз видно что вариант ltree работает существенно медленнее. Хотя, этого следовало ожидать. В nested sets запросы идут по индексируемым числовым полям. Сомневаюсь что быстрее возможно.
Однако, следует отметить что величины времен запросов в варианте ltree вполне приемлимы. Для себя сделал вывод что этот метод вполне заслуживает свое место в разработке. Например в вариантах, когда требуется частное изменение или добавление элементов в дереве.
Создание nested sets после формирования базы — andyhost.ru/files/kladr_nested_sets.pl
А вообще, очень раз да него. Теперь главное что-бы тепличные бытовые условия не вызвали застой в разработке. :)
И подумайте, все-таки над тем, что API на самом деле может стать той «изюминкой» которая может помочь вам продвинуться. Низкий TTL — это приятная, но обычная фишка при установке стандартного DNS сервера самостоятельно. А вот API клиент так просто не получит.
А вот если будет API (когда оно будет) для быстрого динамического изменения параметров DNS — вот это будет то, что оправдает текущую цену. Если, конечно, она не повыситься к тому времени.
Если не смогли отследить кто руководил командой, неужели настолько сложно отследить движение денег через платный номер? Его-же, наверное, кто-то регистрировал, заключал договор… Потом деньги перечислялись на определенные счета. Это-же все можно отследить, по идее.