Início / dba / Perguntas / 335518
Accepted
BinaryVeil
Asked: 2024-02-05 17:49:37 +0800 CST

Referenciando informações da mesma tabela no Postgresql

  • 772

Eu tenho uma tabela geográfica que contém

  • países
  • localidades (cidade, vila, vila, ilha, arquipélago)
  • locais (local/empresa + bairros/distrito/área), por exemplo - Big Ben ou Southwark Borough.

Para detalhes adicionais de cada tipo de local, tenho tabelas relacionadas.
Tabela 'country_details' para locais do tipo 'country' e da mesma forma para locais.

Para um local como 'Big Ben', tem referência ao id da sua localidade (ou seja, Londres), e também referência ao país (que pode ser simplesmente pelo iso_code do país)

Exemplo:

 id |     title      |  locality_id  |  country_iso_code |
---------------------------------------------------------|
 1  | United Kingdom |     null      |     UK            |
 2  | London         |     null      |     UK            |
 3  | Big Ben        |      2        |     UK            |
 4  | XYZ District   |      2        |     UK            |

Cenário

Agora, como para enviar ao cliente informações sobre o Big Ben eu também gostaria de obter o nome da localidade (Londres) e do país (Reino Unido), parece que minhas únicas 2 opções são:

  1. CTE recursivo
  2. JOIN na mesma mesa.

No entanto, uma vez que temos uma tabela de dezenas de milhares de registros, que pode potencialmente crescer para muito mais (alguns milhões), além da complexidade da consulta, isso também afetará o desempenho, suponho.

Pergunta

Qual é a melhor opção para "juntar" detalhes como "Londres" e "Reino Unido"?
Ambas as opções são ruins e é melhor repensar o design do esquema?

Tabelas:

CREATE TABLE places (
    id              int,
    type            smallint, -- ['country', 'locality', 'location']
    sub_type        smallint, -- nullable (city, village, etc.)

    -- names
    title           text,

    -- locality
    locality_name   text,
    locality_id     

    -- country
    country_iso_alpha2 text, -- 'GB'
    country_name       text, -- 'United Kingdom'
    admin_region       text, -- 'England', 'Texas', .. (null for Country)
    
    ...
);

CREATE TABLE country_details(
    place_id      int,
    place_type    smallint NOT NULL CHECK (item_type=1),

    iso_alpha2    text,
    iso_alpha3    text,
    ...

    PRIMARY KEY (place_id, place_type),
    FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);

CREATE TABLE location_details(
    place_id      int,
    place_type    smallint NOT NULL CHECK (item_type=3),

    website            text,
    neighborhood       text,
    formatted_address  text,
    ...

    PRIMARY KEY (place_id, place_type),
    FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);
postgresql

2 respostas

  1. parece que minhas únicas 2 opções são:

    1. CTE recursivo
    2. JOIN na mesma mesa.

    Se for um número fixo de junções e um número pequeno, então, para simplificar, eu diria que escolha a opção 2 e faça algumas auto-junções.

    Se houver muita variabilidade na profundidade hierárquica dos dados, eu diria que escolha a opção nº 1 e use um CTE recursivo.

    No entanto, uma vez que temos uma tabela de dezenas de milhares de registros, que pode potencialmente crescer para muito mais (alguns milhões), além da complexidade da consulta, isso também afetará o desempenho, suponho.

    Para a solução de autojunção, alguns milhões de linhas são pequenos e, quando indexados corretamente, a diferença de algumas centenas de linhas é insignificante.

    Para a solução CTE recursiva, ela ainda deve ter bastante desempenho em alguns milhões de linhas, quando indexada corretamente. Mas você pode notar uma ligeira regressão, como levar menos de um segundo para algumas centenas de linhas e levar alguns segundos para alguns milhões de linhas.

    • 1
  2. Best Answer

    É uma árvore, então vamos construir um exemplo de árvore com 10 folhas por nível e 7 níveis, ou seja, cerca de 1,1 milhão de linhas.

    -- create raw data
CREATE UNLOGGED TABLE tree1 (
    id          INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    parent_id   INTEGER NULL,
    level       INTEGER NOT NULL
);

INSERT INTO tree1 (id, parent_id, level) VALUES (0,NULL,0);
INSERT INTO tree1 (parent_id,level) SELECT id,level+1 FROM tree1 CROSS JOIN generate_series(1,10) WHERE level=0;
INSERT INTO tree1 (parent_id,level) SELECT id,level+1 FROM tree1 CROSS JOIN generate_series(1,10) WHERE level=1;
INSERT INTO tree1 (parent_id,level) SELECT id,level+1 FROM tree1 CROSS JOIN generate_series(1,10) WHERE level=2;
INSERT INTO tree1 (parent_id,level) SELECT id,level+1 FROM tree1 CROSS JOIN generate_series(1,10) WHERE level=3;
INSERT INTO tree1 (parent_id,level) SELECT id,level+1 FROM tree1 CROSS JOIN generate_series(1,10) WHERE level=4;
INSERT INTO tree1 (parent_id,level) SELECT id,level+1 FROM tree1 CROSS JOIN generate_series(1,10) WHERE level=5;

-- create table with paths
CREATE UNLOGGED TABLE tree (
    id          INTEGER NOT NULL,
    parent_id   INTEGER NULL,
    path        INTEGER[] NOT NULL,
    level       INTEGER GENERATED ALWAYS AS (array_length(path,1)) STORED
);

-- populate
WITH RECURSIVE st AS (
    -- select root
    SELECT t.id, t.parent_id, ARRAY[t.id] path FROM tree1 t WHERE t.id=0
  UNION ALL
    SELECT t.id, t.parent_id, path || t.id 
    FROM tree1 t JOIN st ON (t.parent_id=st.id)
)
INSERT INTO tree (id, parent_id, path) SELECT * FROM st;

DROP TABLE tree1;

ALTER TABLE tree ADD PRIMARY KEY (id);
CREATE INDEX ON tree( parent_id );
CREATE INDEX ON tree( path );

VACUUM ANALYZE tree;

    Agora vamos levar uma folha, junto com todos os seus pais, até a raiz. Existem vários métodos.

    • Usando o caminho

    Foi assim que foi feito antes COM RECURSIVO. Funciona bem:

    -- get one node and parents using path
SELECT * FROM 
  (SELECT unnest(path) id FROM tree WHERE id=1000000) p 
  JOIN tree USING (id);

 Nested Loop  (cost=0.85..92.95 rows=10 width=60) (actual time=0.092..0.152 rows=7 loops=1)
   ->  ProjectSet  (cost=0.43..8.50 rows=10 width=4) (actual time=0.071..0.078 rows=7 loops=1)
         ->  Index Scan using tree_pkey on tree tree_1  (cost=0.43..8.45 rows=1 width=48) (actual time=0.064..0.067 rows=1 loops=1)
               Index Cond: (id = 1000000)
   ->  Index Scan using tree_pkey on tree  (cost=0.43..8.45 rows=1 width=60) (actual time=0.008..0.008 rows=1 loops=7)
         Index Cond: (id = (unnest(tree_1.path)))
 Planning Time: 0.342 ms
 Execution Time: 0.214 ms
    • Usando COM RECURSIVO

    Esta é a opção padrão. Ele não usa o caminho, então esta coluna pode ser removida, a menos que seja usada para outra coisa.

    -- get one node and parents using WITH
WITH RECURSIVE st AS (
    -- select root
    SELECT * FROM tree WHERE id=1000000
  UNION ALL
    SELECT tree.* FROM tree JOIN st ON (tree.id=st.parent_id)
)
SELECT * FROM st;

 CTE Scan on st  (cost=855.96..857.98 rows=101 width=44) (actual time=0.037..0.204 rows=7 loops=1)
   CTE st
     ->  Recursive Union  (cost=0.43..855.96 rows=101 width=60) (actual time=0.035..0.193 rows=7 loops=1)
           ->  Index Scan using tree_pkey on tree  (cost=0.43..8.45 rows=1 width=60) (actual time=0.033..0.036 rows=1 loops=1)
                 Index Cond: (id = 1000000)
           ->  Nested Loop  (cost=0.43..84.65 rows=10 width=60) (actual time=0.019..0.019 rows=1 loops=7)
                 ->  WorkTable Scan on st st_1  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.001 rows=1 loops=7)
                 ->  Index Scan using tree_pkey on tree tree_1  (cost=0.43..8.45 rows=1 width=60) (actual time=0.015..0.015 rows=1 loops=7)
                       Index Cond: (id = st_1.parent_id)
 Planning Time: 0.409 ms
 Execution Time: 0.269 ms

    Conclusão: ambas as opções são muito rápidas, menos de 1ms. Nenhum vencedor claro. Não é surpreendente, pois tudo o que fazem é buscar um pequeno número de linhas por meio da chave primária indexada.

    • Usando JOINs

    Não estou considerando isso porque imporia uma profundidade máxima fixa à árvore e retornaria linhas em um formato inconveniente para uma árvore (ou seja, com uma tonelada de colunas).

    No entanto, no meu exemplo de árvore, todas as folhas desta árvore têm o mesmo formato. Os níveis de subdivisão que você está usando não.

    Se sua profundidade for fixa (países> localidades> locais) e você tiver certeza de que nunca precisará subdividir em condados, quarteirões, sub-bairros ou outras coisas... então o método JOIN faz sentido porque o formato de linha que foi antes inconveniente agora se torna conveniente, já que você está lidando com três tipos diferentes de subdivisões, em três tabelas diferentes, e todas elas possuem colunas diferentes.

    Na verdade, com o método JOIN, você pode obter o resultado completo em uma consulta. Com os outros dois, depois de obter os IDs do caminho da tabela em árvore, você terá que consultar as três tabelas de subdivisão separadamente, o que adiciona mais trabalho.

    Isso será bem dimensionado, porque as linhas mais atingidas são os níveis baixos da árvore, que praticamente sempre serão armazenados em cache na RAM.

    • 1

relate perguntas