Início / dba / Perguntas / 61084
Accepted
Dr.YSG
Asked: 2014-03-18 07:00:31 +0800 CST

Índice composto na tabela PostgreSQL de 43 milhões

  • 772

Esta questão está relacionada a uma anterior que fiz: Ordem das colunas em um índice composto no PostgreSQL (e ordem da consulta)

Em vez de sobrecarregar essa pergunta, acho que posso aguçar e limitar minha pergunta aqui. Dada a seguinte consulta (e EXPLAIN ANALYZE), o índice composto que estou criando está ajudando?

Esta primeira consulta foi executada apenas com índices simples (um GIST no esboço) e um (BTREE no pid).

A consulta é:

EXPLAIN ANALYZE SELECT DISTINCT ON (path) oid, pid, product_name, type, path, size 
FROM portal.inventory AS inv 
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((21.51947021484375 51.55059814453125, 18.9129638671875 51.55059814453125, 18.9129638671875 48.8287353515625, 21.51947021484375 48.8287353515625, 21.51947021484375 51.55059814453125))'), inv.outline) 
AND (inv.pid in (20010,20046))

--

O resultado foi o seguinte (que é mais rápido, mas talvez seja apenas porque o banco de dados estava quente).

"Unique  (cost=581.76..581.76 rows=1 width=89) (actual time=110.436..110.655 rows=249 loops=1)"
"  ->  Sort  (cost=581.76..581.76 rows=1 width=89) (actual time=110.434..110.477 rows=1377 loops=1)"
"        Sort Key: path"
"        Sort Method: quicksort  Memory: 242kB"
"        ->  Bitmap Heap Scan on inventory inv  (cost=577.48..581.75 rows=1 width=89) (actual time=39.257..105.878 rows=1377 loops=1)"
"              Recheck Cond: ((pid = ANY ('{20010,20046}'::integer[])) AND ('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline))"
"              Rows Removed by Index Recheck: 3731"
"              Filter: (_st_distance('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography, outline, 0::double precision, false) < 1e-005::double precision)"
"              Rows Removed by Filter: 533"
"              ->  BitmapAnd  (cost=577.48..577.48 rows=1 width=0) (actual time=38.972..38.972 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on inventory_pid_idx  (cost=0.00..123.82 rows=6204 width=0) (actual time=1.116..1.116 rows=7836 loops=1)"
"                          Index Cond: (pid = ANY ('{20010,20046}'::integer[]))"
"                    ->  Bitmap Index Scan on inventory_outline_idx  (cost=0.00..453.41 rows=8212 width=0) (actual time=37.765..37.765 rows=63112 loops=1)"
"                          Index Cond: ('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline)"
"Total runtime: 110.731 ms"

Agora aqui está o resultado com o índice composto adicionado: (observe que o tempo absoluto foi mais lento)

"Unique  (cost=37.81..37.82 rows=1 width=89) (actual time=2464.353..2464.561 rows=249 loops=1)"
"  ->  Sort  (cost=37.81..37.82 rows=1 width=89) (actual time=2464.349..2464.389 rows=1377 loops=1)"
"        Sort Key: path"
"        Sort Method: quicksort  Memory: 242kB"
"        ->  Bitmap Heap Scan on inventory inv  (cost=33.54..37.80 rows=1 width=89) (actual time=2361.018..2459.653 rows=1377 loops=1)"
"              Recheck Cond: (('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline) AND (pid = ANY ('{20010,20046}'::integer[])))"
"              Filter: (_st_distance('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography, outline, 0::double precision, false) < 1e-005::double precision)"
"              Rows Removed by Filter: 533"
"              ->  Bitmap Index Scan on inventory_compound_idx  (cost=0.00..33.53 rows=1 width=0) (actual time=2321.684..2321.684 rows=1910 loops=1)"
"                    Index Cond: (('0103000020E6100000010000000500000000000000FC843540000000007AC6494000000000B8E93240000000007AC6494000000000B8E9324000000000146A484000000000FC84354000000000146A484000000000FC843540000000007AC64940'::geography && outline) AND (pid = ANY ('{20010,20046}'::integer[])))"
"Total runtime: 2558.022 ms"

Finalmente, aqui está a definição da tabela:

CREATE TABLE portal.inventory
(
  oid bigint,
  product_name character varying(100),
  type character varying(25),
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying(200),
  outline geography(Polygon,4326)
)
WITH (
  OIDS=FALSE
);


CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);


CREATE INDEX inventory_outline_idx
  ON portal.inventory
  USING gist
  (outline);


CREATE INDEX inventory_pid_idx
  ON portal.inventory
  USING btree
  (pid);

ATUALIZAÇÃO: Respostas às perguntas listadas abaixo:

Posso adaptar a tabela, mas estou tentando manter as linhas finas. Suas sugestões são variadas, tipos, etc. são coisas que eu gostaria de mudar.

Basicamente, cada linha representa um pouco de metadados sobre um arquivo de imagem geoespacial. Estamos administrando 50 milhões, e isso pode crescer para centenas de milhões ou mais. No banco de dados, cada arquivo é referenciado por um OID exclusivo (desculpe pela duplicação do termo). Eles são agrupados por "produtos" onde PID é o ID do produto. Pode haver cerca de 1.000 OIDs por produto. Cada arquivo de imagem possui uma caixa delimitadora geoespacial (o contorno). Isso é realmente tudo o que preciso para pesquisar. O restante dos dados não será nulo (tipo é uma string de texto, tamanho é o tamanho do arquivo, data é a data em que o arquivo foi criado e caminho é um caminho de arquivo UNC para o arquivo).

Agora, aqui está o motivo pelo qual ordenei a consulta por esboço e, em seguida, PID. Os produtos serão agrupados geoespacialmente. Portanto, todas as linhas OID para Cracóvia, Polônia, estarão localizadas fisicamente na mesma região. Portanto, suponho que, se eu reduzir o balde para uma região pequena, o segundo índice será bem pequeno (digamos, cerca de 100 produtos para uma região da cidade). Que a cláusula IN( ..) retirará.

Os valores reais do PIDS foram retirados da outra pergunta que postei aqui. Mas essa tabela é apenas para produtos e, portanto, seu tamanho é de cerca de 30K, o que significa pesquisas rápidas e sem necessidade de consultas compostas.

Eu me pergunto se o planejador POSTGreSQL é inteligente o suficiente para decidir se um índice composto por (contorno, pid) é mais rápido que (pid, esboço) se ambos os índices estiverem lá. Bem, acho que posso testar.

postgresql index

2 respostas

  1. Best Answer

    Em um índice GiST, a ordem das colunas tem um significado diferente do que em um índice B-tree . Por documentação:

    Um índice GiST de várias colunas pode ser usado com condições de consulta que envolvam qualquer subconjunto das colunas do índice. As condições nas colunas adicionais restringem as entradas retornadas pelo índice, mas a condição na primeira coluna é a mais importante para determinar quanto do índice precisa ser verificado. Um índice GiST será relativamente ineficaz se sua primeira coluna tiver apenas alguns valores distintos, mesmo se houver muitos valores distintos em colunas adicionais.

    Resumindo: coloque as colunas mais seletivas primeiro.

    Sua EXPLAINsaída mostra que a condição ativada pidé mais seletiva ( rows=7836) do que a ativada outline( rows=63112). Se isso puder ser generalizado (um único exemplo pode ser enganoso), sugiro esta alternativa:

    CREATE INDEX inventory_compound_idx ON portal.inventory USING gist (pid, outline);

    Se a maioria de suas consultas (importantes) incluir condições em ambas as colunas, um índice de várias colunas pode ser útil. Caso contrário, colunas únicas podem ser melhores no geral.

    Disposição da tabela

    Este é um palpite, já que não tenho informações completas.

    • Não use oidcomo nome de coluna. É fácil confundir com o OID.

    • Não use o nome datepara uma coluna de carimbo de data/hora. Ou melhor: não use o nome datepara nenhuma coluna, não use nomes de tipos-base para identificadores. Pode levar a erros confusos e mensagens de erro.

    • Crie uma tabela de pesquisa para tipos e coloque apenas um pequeno número inteiro type_idna tabela grande. Embale bem os tipos de comprimento fixo para não desperdiçar espaço com preenchimento. Detalhes.

    • Eu prefiro o tipo text(ou varcharsem limite de comprimento) sobre varchar(n). Detalhes.

    Por exemplo:

    CREATE TABLE portal.inventory (
   inventory_id bigint PRIMARY KEY
  ,type_id      integer NOT NULL REFERENCES inv_type(type_id)
  ,pid          integer NOT NULL
  ,size         bigint NOT NULL
  ,ts           timestamp NOT NULL
  ,outline      geography(Polygon,4326)
  ,product_name text
  ,path         text
);
    • 8

  2. Erwin, eu tenho alguns dos dados que você pediu. Desta vez, tentei uma consulta mais ambiciosa (espero que a maior parte do trabalho seja para um conjunto menor de pids e uma região geoespacial menor), mas algumas pessoas vão sobrecarregar o sistema e isso pode acabar não sendo a maior consulta no final.

    SELECT DISTINCT ON (path)  pid, type, path, size 
FROM portal.inventory AS inv 
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((21.2310791015625 51.416015625, 18.643798828125 51.416015625, 18.643798828125 48.69415283203125, 21.2310791015625 48.69415283203125, 21.2310791015625 51.416015625))'), inv.outline) 
AND (inv.pid in (23869,23869,23599,23869,14153,14156,110,19131,19131,19164,91,23501,36,23501,23586,23586,23586,23586,23586,23599,23599,20047,113,120,3,120,23,118,82,120,113,113,120,129,129,210,339,339,341,23345,23506,23559,23553,23546,23546,23765,20010,19939,19939,20043,20046,20046,20046,20046,20047,23345,23345,23507,23507,129,23589,23612,23612,23539,23539,23539,23553,23553,23553,23559,23596,23589,23594,23589,23589,23596,23596,23596,23506,23506,23511,23511,23742,23742,23846,23846,23846,23742,23765,23765,341,19939,20047,23612,62,150,150,150,150,150,150,268,268,268,268,23598,120,23501))

    Explique a análise do índice com (contorno, pid)

    [O que é estranho aqui é que, embora a consulta especifique que a cláusula WHERE tem o esboço primeiro e o pid depois - e, portanto, deveria estar usando o índice Inventory_compound_idx, ela está usando o índice inverso icompound_idx]

    "Unique  (cost=10788.37..10792.36 rows=1 width=76) (actual time=3042.605..3120.677 rows=1682 loops=1)"
"  ->  Sort  (cost=10788.37..10790.37 rows=799 width=76) (actual time=3042.600..3114.823 rows=48341 loops=1)"
"        Sort Key: path"
"        Sort Method: external merge  Disk: 4384kB"
"        ->  Bitmap Heap Scan on inventory inv  (cost=503.88..10749.85 rows=799 width=76) (actual time=119.501..2586.973 rows=48341 loops=1)"
"              Recheck Cond: ((pid = ANY ('{23869,23869,23599,23869,14153,14156,110,19131,19131,19164,91,23501,36,23501,23586,23586,23586,23586,23586,23599,23599,20047,113,120,3,120,23,118,82,120,113,113,120,129,129,210,339,339,341,23345,23506,23559,23553,23546,23546,23765,20010,19939,19939,20043,20046,20046,20046,20046,20047,23345,23345,23507,23507,129,23589,23612,23612,23539,23539,23539,23553,23553,23553,23559,23596,23589,23594,23589,23589,23596,23596,23596,23506,23506,23511,23511,23742,23742,23846,23846,23846,23742,23765,23765,341,19939,20047,23612,62,150,150,150,150,150,150,268,268,268,268,23598,120,23501}'::integer[])) AND ('0103000020E6100000010000000500000000000000283B35400000000040B5494000000000D0A432400000000040B5494000000000D0A4324000000000DA58484000000000283B354000000000DA58484000000000283B35400000000040B54940'::geography && outline))"
"              Rows Removed by Index Recheck: 370361"
"              Filter: (_st_distance('0103000020E6100000010000000500000000000000283B35400000000040B5494000000000D0A432400000000040B5494000000000D0A4324000000000DA58484000000000283B354000000000DA58484000000000283B35400000000040B54940'::geography, outline, 0::double precision, false) < 1e-005::double precision)"
"              Rows Removed by Filter: 15439"
"              ->  Bitmap Index Scan on inventory_icompound_idx  (cost=0.00..503.68 rows=2398 width=0) (actual time=117.783..117.783 rows=219595 loops=1)"
"                    Index Cond: ((pid = ANY ('{23869,23869,23599,23869,14153,14156,110,19131,19131,19164,91,23501,36,23501,23586,23586,23586,23586,23586,23599,23599,20047,113,120,3,120,23,118,82,120,113,113,120,129,129,210,339,339,341,23345,23506,23559,23553,23546,23546,23765,20010,19939,19939,20043,20046,20046,20046,20046,20047,23345,23345,23507,23507,129,23589,23612,23612,23539,23539,23539,23553,23553,23553,23559,23596,23589,23594,23589,23589,23596,23596,23596,23506,23506,23511,23511,23742,23742,23846,23846,23846,23742,23765,23765,341,19939,20047,23612,62,150,150,150,150,150,150,268,268,268,268,23598,120,23501}'::integer[])) AND ('0103000020E6100000010000000500000000000000283B35400000000040B5494000000000D0A432400000000040B5494000000000D0A4324000000000DA58484000000000283B354000000000DA58484000000000283B35400000000040B54940'::geography && outline))"
"Total runtime: 3125.598 ms"

    E agora mudei a ordem da consulta para que o PID seja o primeiro na cláusula where, então ele deve usar o índice icompound_idx (pid, estrutura de tópicos).

    (Você vê alguma vantagem real, eu não).

    "Unique  (cost=10788.37..10792.36 rows=1 width=76) (actual time=3030.431..3108.313 rows=1682 loops=1)"
"  ->  Sort  (cost=10788.37..10790.37 rows=799 width=76) (actual time=3030.429..3102.474 rows=48341 loops=1)"
"        Sort Key: path"
"        Sort Method: external merge  Disk: 4384kB"
"        ->  Bitmap Heap Scan on inventory inv  (cost=503.88..10749.85 rows=799 width=76) (actual time=110.656..2575.282 rows=48341 loops=1)"
"              Recheck Cond: ((pid = ANY ('{23869,23869,23599,23869,14153,14156,110,19131,19131,19164,91,23501,36,23501,23586,23586,23586,23586,23586,23599,23599,20047,113,120,3,120,23,118,82,120,113,113,120,129,129,210,339,339,341,23345,23506,23559,23553,23546,23546,23765,20010,19939,19939,20043,20046,20046,20046,20046,20047,23345,23345,23507,23507,129,23589,23612,23612,23539,23539,23539,23553,23553,23553,23559,23596,23589,23594,23589,23589,23596,23596,23596,23506,23506,23511,23511,23742,23742,23846,23846,23846,23742,23765,23765,341,19939,20047,23612,62,150,150,150,150,150,150,268,268,268,268,23598,120,23501}'::integer[])) AND ('0103000020E6100000010000000500000000000000283B35400000000040B5494000000000D0A432400000000040B5494000000000D0A4324000000000DA58484000000000283B354000000000DA58484000000000283B35400000000040B54940'::geography && outline))"
"              Rows Removed by Index Recheck: 370361"
"              Filter: (_st_distance('0103000020E6100000010000000500000000000000283B35400000000040B5494000000000D0A432400000000040B5494000000000D0A4324000000000DA58484000000000283B354000000000DA58484000000000283B35400000000040B54940'::geography, outline, 0::double precision, false) < 1e-005::double precision)"
"              Rows Removed by Filter: 15439"
"              ->  Bitmap Index Scan on inventory_icompound_idx  (cost=0.00..503.68 rows=2398 width=0) (actual time=109.132..109.132 rows=219595 loops=1)"
"                    Index Cond: ((pid = ANY ('{23869,23869,23599,23869,14153,14156,110,19131,19131,19164,91,23501,36,23501,23586,23586,23586,23586,23586,23599,23599,20047,113,120,3,120,23,118,82,120,113,113,120,129,129,210,339,339,341,23345,23506,23559,23553,23546,23546,23765,20010,19939,19939,20043,20046,20046,20046,20046,20047,23345,23345,23507,23507,129,23589,23612,23612,23539,23539,23539,23553,23553,23553,23559,23596,23589,23594,23589,23589,23596,23596,23596,23506,23506,23511,23511,23742,23742,23846,23846,23846,23742,23765,23765,341,19939,20047,23612,62,150,150,150,150,150,150,268,268,268,268,23598,120,23501}'::integer[])) AND ('0103000020E6100000010000000500000000000000283B35400000000040B5494000000000D0A432400000000040B5494000000000D0A4324000000000DA58484000000000283B354000000000DA58484000000000283B35400000000040B54940'::geography && outline))"
"Total runtime: 3113.334 ms"
    • 0

relate perguntas