Поиск подчиненных адресов в КЛАДР

Для поиска будем использовать заданную структуру.

Создадим представление:

CREATE OR REPLACE VIEW W_LOCALITY_VIEW(ID, PREFIX, NAME, PARENT_ID) AS

SELECT W_LOCALITY.ID AS ID, W_LOCALITY_TYPE.NAME AS PREFIX, W_LOCALITY.NAME AS NAME, W_LOCALITY.PARENT_ID AS PARENT_ID

FROM

W_LOCALITY INNER JOIN W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID;

Поисковые поля:

1. PARENT_ID

Возможные значения:

1. PARENT_ID IS NULL - корневые объекты.
2. PARENT_ID = 'value' - заданный объект.

Создадим индексы:

1. CREATE UNIQUE INDEX W_LOCALITY_PARENT_ID_IDX on W_LOCALITY(ID, PARENT_ID);
2. CREATE UNIQUE INDEX W_LOCALITY_FPARENT_ID_IDX on W_LOCALITY(PARENT_ID, ID);
3. CREATE UNIQUE INDEX W_LOCALITY_NULL_PARENT_ID_IDX on W_LOCALITY(ID) WHERE PARENT_ID IS NULL;

Поисковые запросы:

1. Поиск корневых объектов:
   set enable_seqscan = off;
   SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;
2. Поиск дочерних объектов заданного объекта:
   set enable_seqscan = off;
   SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;

Скорость обработки:

Поиск корневых объектов	30.248 мсек
Поиск дочерних объектов заданного объекта	15.166 мсек

Размер таблиц:

W_LOCALITY	23,244,680
W_LOCALITY_TYPE	114

Вывод данных

1. SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;

   ID	PREFIX	NAME	PARENT_ID
   1331	Респ	Калмыкия	NULL
   1770	Респ	Марий Эл	NULL
   2992	Респ	Удмуртская	NULL
   3775	край	Алтайский	NULL
   ....	....	....	....
   208565	г	Севастополь	NULL

2. SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;

   ID	PREFIX	NAME	PARENT_ID
   112563	г	Воронеж	112562
   164621	г	Воронеж-45	112562
   34380	г	Лиски	112562
   8262	г	Нововоронеж	112562
   ....	....	....	....
   8314	р-н	Калачеевский	112562

3. SELECT * FROM W_LOCALITY_VIEW WHERE ID=112562

   ID	PREFIX	NAME	PARENT_ID
   112562	обл	Воронежская	NULL