PostgreSQL. ltree. Выборки потомков.

[mshurutov]

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

• Все потомки
  
  
  • Рекурсивный запрос
    
  
  
  explain analyze with recursive kr (parent_code, code) as (
  select k.parent_code, k.code from kladr k where k.parent_code='5000000000000'
  union
  select k.parent_code, k.code from kladr k join kr on k.parent_code = kr.code)
  select * from kr;
  QUERY PLAN
  --------------------------------------------------------------------------------------------------------------------------------------------------------------
  CTE Scan on kr (cost=44832.11..53929.73 rows=454881 width=112) (actual time=0.038..65.915 rows=7103 loops=1)
  CTE kr
  -> Recursive Union (cost=0.42..44832.11 rows=454881 width=28) (actual time=0.037..63.310 rows=7103 loops=1)
  -> Index Only Scan using kladr_pcode_code_idx on kladr k (cost=0.42..5.49 rows=61 width=28) (actual time=0.031..0.051 rows=76 loops=1)
  Index Cond: (parent_code = '5000000000000'::bpchar)
  Heap Fetches: 0
  -> Nested Loop (cost=0.42..3572.90 rows=45482 width=28) (actual time=4.552..19.787 rows=2342 loops=3)
  -> WorkTable Scan on kr kr_1 (cost=0.00..12.20 rows=610 width=56) (actual time=0.000..0.325 rows=2368 loops=3)
  -> Index Only Scan using kladr_pcode_code_idx on kladr k_1 (cost=0.42..5.09 rows=75 width=28) (actual time=0.008..0.008 rows=1 loops=7103)
  Index Cond: (parent_code = kr_1.code)
  Heap Fetches: 0
  Total runtime: 67.574 ms
  (12 строк)
  
  • Список из ltree
    
  
  
  kladr=> explain analyze select parent_code, code from kladr where hierarch ~ '50.*{1,}';
  QUERY PLAN
  ----------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on kladr (cost=29.75..709.90 rows=190 width=28) (actual time=1.359..3.927 rows=7103 loops=1)
  Recheck Cond: (hierarch ~ '50.*{1,}'::lquery)
  -> Bitmap Index Scan on kladr_hierarch_gist (cost=0.00..29.71 rows=190 width=0) (actual time=1.328..1.328 rows=7103 loops=1)
  Index Cond: (hierarch ~ '50.*{1,}'::lquery)
  Total runtime: 4.364 ms
  (5 строк)
  
  Время: 4,704 мс
  
  Можно в заключительно запросе поискать по hierarch <@ '50' and hierarch != '50', но такая модификация не дает никакого выигрыша. Но и не проигрывает во времени выполнения запроса.
  
  
  
  
• Непосредственные потомки
  
  
  • Запрос по предку
    
  
  
  kladr=> explain analyze select parent_code, code from kladr where parent_code='5000000000000';
  QUERY PLAN
  ------------------------------------------------------------------------------------------------------------------------------------
  Index Only Scan using kladr_pcode_code_idx on kladr (cost=0.42..5.49 rows=61 width=28) (actual time=0.028..0.046 rows=76 loops=1)
  Index Cond: (parent_code = '5000000000000'::bpchar)
  Heap Fetches: 0
  Total runtime: 0.066 ms
  (4 строки)
  
  • Список из ltree
    
  
  
  kladr=> explain analyze select parent_code,code from kladr where hierarch ~ '50.000.000.*{1}' or hierarch ~ '50.000.*{1}' or hierarch ~ '50.*{1}';
  QUERY PLAN
  --------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on kladr (cost=89.55..1922.92 rows=568 width=28) (actual time=1.059..1.135 rows=76 loops=1)
  Recheck Cond: ((hierarch ~ '50.000.000.*{1}'::lquery) OR (hierarch ~ '50.000.*{1}'::lquery) OR (hierarch ~ '50.*{1}'::lquery))
  -> BitmapOr (cost=89.55..89.55 rows=569 width=0) (actual time=1.049..1.049 rows=0 loops=1)
  -> Bitmap Index Scan on kladr_hierarch_gist (cost=0.00..29.71 rows=190 width=0) (actual time=0.065..0.065 rows=8 loops=1)
  Index Cond: (hierarch ~ '50.000.000.*{1}'::lquery)
  -> Bitmap Index Scan on kladr_hierarch_gist (cost=0.00..29.71 rows=190 width=0) (actual time=0.102..0.102 rows=32 loops=1)
  Index Cond: (hierarch ~ '50.000.*{1}'::lquery)
  -> Bitmap Index Scan on kladr_hierarch_gist (cost=0.00..29.71 rows=190 width=0) (actual time=0.882..0.882 rows=36 loops=1)
  Index Cond: (hierarch ~ '50.*{1}'::lquery)
  Total runtime: 1.174 ms
  
  Но это конкретная база, по которой напильник толком не проходил. На самом деле запрос д/б таким
  
  kladr=# explain analyze select parent_code,code from kladr where hierarch ~ '50.*{1}';
  QUERY PLAN
  --------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on kladr (cost=29.75..709.90 rows=190 width=28) (actual time=1.003..1.055 rows=36 loops=1)
  Recheck Cond: (hierarch ~ '50.*{1}'::lquery)
  -> Bitmap Index Scan on kladr_hierarch_gist (cost=0.00..29.71 rows=190 width=0) (actual time=0.993..0.993 rows=36 loops=1)
  Index Cond: (hierarch ~ '50.*{1}'::lquery)
  Total runtime: 1.079 ms
  (5 строк)
  Ну как бы понятно. Нет в мире совершенства, а в жизни счастья, ну и все такое...
  
  
  

Что я хотел сказать вообще? Да черт его знает, по большому счету. Пока базу готовил, планов было громадье, запросов для проверки - море-АкИЯн. Вот весь запал на подготовку базы и ушел, видимо. :(
Одно можно сказать с некоторой долей уверенности: ltree - годная реализация materialized path в PostgreSQL. И когда условия задачи подразумевают использование иерархий, для который materialized path - наиболее подходящее решение, то использовать нужно именно ltree.

PostgreSQL. ltree. Заливка базы.
PostgreSQL. ltree. Инициализация поля parent_code.
PostgreSQL. ltree. Выборки потомков.

ЗЫ. Буду благодарен за вычурные запросы к базе для посмотреть, что получится в каком-либо конкретном случае.