Início / dba / Perguntas / 59922
Accepted
Dr.YSG
Asked: 2014-03-01 08:26:56 +0800 CST

Ordem das colunas em um índice composto no PostgreSQL (e ordem de consulta)

  • 772

Eu tenho uma tabela com 50 mil linhas. Na verdade, é uma tabela PostGIS.

A consulta tem 4 partes (1 obrigatória) (3 opcionais)

  1. caixa de interseção (um retângulo de geografia) com 4 lat,long (eu uso st_intersects) [Obrigatório]
  2. Intervalo de datas (min, max) em um campo de data
  3. Tipo de arquivo (um conjunto de até 8 valores de texto) atualmente usando IN( .....), mas posso fazer disso uma tabela temporária, se necessário. Vejo que muita gente não gosta do IN.
  4. País (um valor de texto).

Espero cerca de 100 a 4.000 linhas retornadas

Se eu criar um índice composto na tabela, qual coluna devo usar primeiro. O mais refinado é provavelmente o local (os dados estão espalhados pelo mundo). Atualmente, tenho-o como índice GIST.

Os outros índices seriam BTREE.

Minha intuição diz que use grãos finos e claro por último. Por exemplo, existem apenas cerca de 12 tipos de arquivo, portanto, seriam baldes muito grandes para o índice.

O que dizem os gurus PostgreSQL e PostGIS (que conhecem as partes internas do sistema)?

ATUALIZAR:

Deixe-me aguçar esta questão.

  1. Não quero que ninguém tenha que fazer o trabalho que devo fazer. Respeito demais o seu tempo. Então, irei explicar a análise mais tarde.
  2. Tudo o que eu estava procurando eram alguns indicadores, dicas e diretrizes.
  3. Eu li esta pequena postagem excelente: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes sobre índices
  4. O que eu normalmente faço é criar 4 índices separados (caixa geográfica, nome do país, tipo_de_arquivo e data), mas o que quero ver é o que uma consulta composta faria.

Diga-me se alguma dessas suposições está errada. (Sou muito novo na ideia de índices compostos)

  1. A ordem é importante. Escolha como primeiro índice aquele que reduzirá mais as linhas (no meu caso, a localização (geografia), que é um polígono simples ou multipolígono, seria o melhor).
  2. Às vezes, as consultas ignoram os índices. Mas se eu criar uma consulta composta com a chave (#1, #2, #3, #4), mesmo que o usuário crie algo que peça #1, #3, o planejador ainda usará a consulta composta única, pois eles solicitam é mantido.
  3. Normalmente, eu criaria três consultas BTREE e uma GIST (para o tipo de geografia). O PostGIS não suporta a criação de um composto de vários tipos de índice. Então terei que usar GIST o índice composto. Mas isso não deve prejudicar as coisas.
  4. Se eu criar alguns índices adicionais compostos ou de valor único, o planejador é inteligente o suficiente para escolher o mais inteligente.....
  5. O nome do país pode ter cerca de 250 valores diferentes e está obviamente fortemente vinculado ao local (geobox), mas se o próximo melhor índice para reduzir o tamanho da linha for file_type, devo usá-lo a seguir. Não espero que os usuários usem país ou data com frequência em seus conjuntos de consulta.
  6. NÃO preciso me preocupar em criar um índice composto de 4 chaves aumentará muito o tamanho dos dados do índice. Ou seja, se um índice de uma chave for 90% do aumento de desempenho, não custa nada adicionar mais 3 itens para torná-lo composto. Por outro lado, eu realmente deveria criar ambos os índices. Um índice de geografia única, e também um índice composto, e deixe o planejador descobrir qual é o melhor, e ele levará em consideração o tamanho da tabela de índices.

Mais uma vez, não estou pedindo a ninguém para projetar minha solução, não perco o trabalho dos outros. Mas preciso de coisas que a documentação do PostGreSQL não me informa sobre implementação

[O motivo pelo qual ainda não tenho um resultado EXPLAIN para mostrar é que preciso criar essa tabela de 25 mil linhas a partir de uma tabela de 24 milhões de linhas. Está demorando mais do que eu pensava. Estou agrupando as coisas em 1.000 grupos de itens e permitindo que o usuário consulte a tabela de 25 mil linhas. Mas minha próxima pergunta envolverá o uso dos resultados dessa consulta para ir para a tabela de linhas MASTER 25M e retirar as coisas, e é aí que o desempenho do índice composto realmente ACERTARÁ].

exemplo de consulta abaixo:

SELECT
    public.product_list_meta_mv.cntry_name       AS country,
    public.product_list_meta_mv.product_producer AS producer,
    public.product_list_meta_mv.product_name     AS prod_name,
    public.product_list_meta_mv.product_type     AS ptype,
    public.product_list_meta_mv.product_size     AS size,
    ST_AsGeoJSON(public.product_list_meta_mv.the_geom, 10, 2)          AS outline
FROM
    public.product_list_meta_mv 
WHERE
    public.product_list_meta_mv.cntry_name = 'Poland' 
AND
    ST_Intersects(public.product_list_meta_mv.the_geom,
    st_geogfromtext('SRID=4326;POLYGON((21.23107910156250 51.41601562500000,
                                        18.64379882812500 51.41601562500000,
                                        18.64379882812500 48.69415283203130,
                                        21.23107910156250 48.69415283203130,
                                        21.23107910156250 51.41601562500000))')) 
AND (date >= '1/2/1900 5:00:00 AM' 
 AND date <= '2/26/2014 10:26:44 PM')
AND (public.product_list_meta_mv.product_type in
    ('CIB10','DTED0','DTED1','DTED2','CIB01','CIB05')) ;

EXPLICAR ANALISAR resultados (não coloquei nenhum índice composto, e pela velocidade que estou vendo não sei se preciso).

"Bitmap Heap Scan on catalog_full cat  (cost=4.33..37.49 rows=1 width=7428) (actual time=1.147..38.051 rows=35 loops=1)"
"  Recheck Cond: ('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography && outline)"
"  Filter: (((type)::text = ANY ('{CADRG,CIB10,DTED1,DTED2}'::text[])) AND (_st_distance('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography, outline, 0::double precision, false) < 1e-005::double precision))"
"  Rows Removed by Filter: 61"
"  ->  Bitmap Index Scan on catalog_full_outline_idx  (cost=0.00..4.33 rows=8 width=0) (actual time=0.401..0.401 rows=96 loops=1)"
"        Index Cond: ('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography && outline)"
"Total runtime: 38.109 ms"

EXPLAIN ANALYZE SELECT pid,product_name,type,country,date,size,cocom,description,egpl_date,ST_AsGeoJSON(outline, 10, 2) AS outline 
FROM portal.catalog_full AS cat 
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((21.2200927734375 51.38031005859375, 18.65478515625 51.38031005859375, 18.65478515625 48.7298583984375, 21.2200927734375 48.7298583984375, 21.2200927734375 51.38031005859375))'), cat.outline) 
AND (cat.type in ('CADRG','CIB10','DTED1','DTED2'))
postgresql postgis

3 respostas

  1. Best Answer

    Como parte do meu trabalho, mantenho um banco de dados PostgreSQL bastante grande (cerca de 120 GB em disco, várias tabelas de vários milhões de linhas) e coletei alguns truques sobre como acelerar as consultas. Primeiro, alguns comentários sobre suas suposições:

    1. Sim, a ordem é importante, mas é apenas o primeiro que é realmente diferente, o resto são índices de segunda classe.
    2. Não tenho certeza se ele sempre usará os dois, meu palpite é que o planejador de consultas usará o número 1 e fará algo inteligente com o restante.
    3. Não tenho experiência com GIST.
    4. Sim, adicione todos os índices primeiro, veja o que é mais usado e o que dá o melhor desempenho.
    5. Sugiro que você experimente os dois e meça o que funciona melhor. Tente reescrever o sql com subconsultas diferentes, talvez país e hora em uma, e junte-se à consulta de interseção. Não notei nenhum problema de desempenho com as cláusulas IN, desde que a lista IN não tenha milhares de elementos. Meu palpite é que algumas consultas diferentes especificamente ajustadas dependendo dos critérios de entrada disponíveis darão os melhores resultados.

    6. Eu sugeriria contra fazer um índice de 4 vias. Tente criar um e verifique o tamanho, eles podem ficar muito grandes. Na minha experiência, quatro índices de 1 chave foram quase tão rápidos quanto um único índice de 4 vias. Um truque que funciona bem para algumas consultas específicas são os índices parciais, ou seja, algo assim:

      CREATE INDEX ON table_x (key1, key2, key3) WHERE some_x_column = 'XXXX';

    Eu criei aliases em meu arquivo .psqlrc com consultas para ajudar a encontrar quais índices adicionar ou remover. Fique à vontade para dar uma olhada no GitHub: .psql

    Eu uso muito :seq_scans e :bigtables, e então \d table_name para obter detalhes sobre a tabela. Não se esqueça de redefinir as estatísticas depois de fazer algumas alterações, selecione pg_stat_reset();

    • 11

  2. Na minha experiência, se você deseja obter um grande número de linhas, o índice composto pode aumentar muito seu desempenho apenas se você adicionar todos os campos em seu SELECT e suas cláusulas WHERE da consulta, para que o Postgres execute um índice somente digitalizar .

    Minhas consultas ficaram mais rápidas cerca de duas ou três vezes depois de usar um índice composto de 5 campos que resultou em uma varredura somente de índice, em vez de índices de campo único.

    Além disso, pela minha experiência, um índice de 5 campos usa cerca de duas vezes o espaço em disco em comparação com um índice de campo único, ambos com o mesmo primeiro campo. (Testado em um banco de dados com cerca de 100 milhões de linhas)

    Finalmente, obrigado ao Claes por suas ótimas dicas , especialmente sobre índices parciais. Eu definitivamente vou tentar.

    • 2

  3. Acho que a coisa mais provável para ajudar (se alguma coisa) seria adicionar product_type como uma segunda coluna ao índice gist. Mas sem saber quantas linhas correspondem a cada uma das condições AND (isolada) para suas consultas típicas/problemáticas, podemos apenas adivinhar.

    Quando abordo isso, a primeira coisa que faço é executar a consulta de forma simplificada, onde a cláusula WHERE tem apenas uma condição, cada uma tomada por vez, em EXPLAIN ANALYZE. Observe as linhas estimadas e as linhas reais de cada uma.

    • 1

relate perguntas