Dr.YSG's questions

No PostgreSQL 9.2 eu não tive nenhum problema em criar um índice que tivesse um tipo de geografia (postGIS) e um inteiro como um índice composto. Mas agora (9.6) ele reclama da criação do índice e não entendo a dica que está fornecendo:

As colunas e dados são todos criados corretamente, o Postgres está reclamando no índice de criação.

ERROR: data type integer has no default operator class for access method "gist" 
HINT: You must specify an operator class for the index 
      or define a default operator class for the data type. 
********** Error**********  
ERROR: data type integer has no default operator class for access method "gist" 
SQL state: 42704 
Hint: You must specify an operator class for the index 
      or define a default operator class for the data type.

A definição do esquema é a seguinte:

- Table: portal.inventory

-- DROP TABLE portal.inventory;

CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE portal.inventory
  OWNER TO postgres;

-- Index: portal.inventory_compound_idx

-- DROP INDEX portal.inventory_compound_idx;

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

-- Index: portal.inventory_icompound_idx

-- DROP INDEX portal.inventory_icompound_idx;

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

Estou executando o PostgreSQL 9.6 no Windows 10

Parece que tem um monitor de atualização que não usa nenhuma informação sobre o agente proxy do Windows (temos um proxy corporativo). Ele também não analisa as variáveis ​​de ambiente HTTP_PROXY ou HTTPS_PROXY (que configurei para adicionar credenciais via CNTLM para nosso firewall corporativo). Isso é útil para programas como NPM e GIT que precisam passar pelo nosso firewall de saída.

Então, estou recebendo a seguinte mensagem a cada 10 ou alguns minutos como um pop-up de um programa chamado:

"D:\Program Files (x86)\postgresql\updatemonitor\bin\UpdManager.exe" --execute  "D:\Program Files\PostgreSQL\9.6\bin\stackbuilder.exe"

A mensagem é muito enganadora, mas eu rastreei o programa, e é o listado acima.

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.

Eu preciso fazer uma junção interna de 25 milhões de linhas em 25 milhões de linhas. A caixa é um Alienware area 51, 4 núcleos 25GB de memória e drive SATA (disco sem sistema). Até agora, foram 22 horas. Eu fiz btree indexar a coluna ID (bigint) na qual a junção está sendo feita para ambas as tabelas. Alguma dica? Quanto tempo você acha que eu tenho que esperar?

EXPLAIN SELECT
    public.products_by_location_mv.id,
    public.products_by_location_mv."data_object.unique_id",
    public.products_by_location_mv.location AS outline,
    public.products_by_location_mv.elevation_ft,
    public.products_by_location_mv."geo_product.geo_product_id" AS pid,
    public.products_by_location_mv.cntry_name,
    public.products_by_location_mv.product_name,
    public.products_by_location_mv.product_type,
    public.products_by_location_mv.product_producer,
    public.products_by_location_mv.product_size,
    public.products_by_location_mv.do_location,
    public.products_by_location_mv.product_location,
    public.obj4.uid AS oid,
    public.obj4.size_bytes,
    public.obj4.object_date,
    public.obj4.description,
    public.obj4.location AS path
INTO
    public.inventory0
FROM
    public.obj4
INNER JOIN
    public.products_by_location_mv
ON
    (
        public.obj4.id = public.products_by_location_mv.id) ;

"Hash Join  (cost=3825983.03..12908235.27 rows=24202368 width=1356)"
"  Hash Cond: (products_by_location_mv.id = obj4.id)"
"  ->  Seq Scan on products_by_location_mv  (cost=0.00..1457298.68 rows=24202368 width=721)"
"  ->  Hash  (cost=1414691.68..1414691.68 rows=25507868 width=643)"
"        ->  Seq Scan on obj4  (cost=0.00..1414691.68 rows=25507868 width=643)"

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'))