Alexandros's questions

Eu tenho duas tabelas left2e right2. Ambas as tabelas serão grandes (1-10 milhões de linhas).

CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);

CREATE TABLE right2( t1 INTEGER, d INTEGER, arr INTEGER[] );
ALTER TABLE right2 ADD PRIMARY KEY(t1,d);

Vou realizar este tipo de consulta:

SELECT l.d + r.d,
       UNIQ(SORT((array_agg_mult(r.arr)))
FROM left2 l,
     right2 r
WHERE l.t1 = r.t1
GROUP BY l.d + r.d
ORDER BY l.d + r.d;

Onde para agregação de arrays utilizo a função:

CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}');

Após concatenar os arrays, utilizo a UNIQfunção do intarraymódulo. Existe uma maneira mais eficiente de fazer isso? Existe algum índice no arrcampo para acelerar a mesclagem (com remoção de duplicatas)? A função agregada pode remover duplicatas diretamente? As matrizes originais podem ser consideradas classificadas (e são únicas) se isso ajudar.

O SQL Fiddle está aqui :

Suponha que eu tenha os seguintes dados:

| f1 | f2 | f3 |
|----|----|----|
|  1 |  1 |  1 |
|  1 |  1 |  5 |
|  1 |  2 |  3 |
|  1 |  2 |  6 |
|  1 |  3 |  4 |
|  1 |  3 |  7 |
|  2 |  1 |  2 |
|  2 |  1 | 22 |
|  2 |  2 |  3 |
|  2 |  2 |  4 |

Existem dois valores máximos de f3 para cada combinação de f1,f2.

Para este exemplo específico, desejo obter os 2 valores mínimos principais por f1 e intervalo (f2 - max(f2) por f1).

Saída de exemplo:

| f1 | RNG| f3 |
|----|----|----|
|  1 |1 -3|  1 |
|  1 |1 -3|  3 |
|  1 |2 -3|  3 |
|  1 |2 -3|  4 |
|  1 |3 -3|  4 |
|  1 |3 -3|  7 |
|  2 |1- 2|  2 |
|  2 |1- 2|  3 |
|  2 |2- 2|  3 |
|  2 |2- 2|  4 |

Para criar o campo RNG não é necessário. Eu apenas adicionei para mostrar que para f1=1, existem 3 intervalos: 1-3, 2-3, 3-3criados pelos valores distintos de f2for f1=1. Para cada intervalo, desejo calcular os valores mínimos top-k por f1 e intervalo.

SQL Fiddle aqui:

http://sqlfiddle.com/#!15/9ddbb/1

Construir os intervalos pode ser feito por:

SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2;

Existe alguma maneira de conseguir isso, sem construir tabelas intermediárias de banco de dados?

Acredito que o PostgreSQL pode registrar consultas lentas ou malsucedidas. Provavelmente também posso definir o PostgreSQL para registrar todas as consultas executadas. Ao contrário, estou interessado em saber se existe uma maneira de um invasor mal-intencionado obter acesso a todas as consultas executadas com sucesso no servidor PostgreSQL, se eu tiver desativado o registro o máximo possível. Isso é possível? Ou, uma vez que uma consulta foi executada com sucesso (pode ser uma consulta SELECT ou UPDATE), posso ter 100% de certeza de que o servidor de banco de dados não possui memória de consultas executadas com sucesso e, portanto, ninguém mais pode obter acesso a essas informações. Estou usando o PostgreSQL 9.3.

Continuando com minha pergunta anterior, PostgreSQL maximiza o desempenho do SSD . A comunidade DBA SE me deu conselhos eficientes sobre as configurações ideais para o PostgreSQL no SSD. O problema é que vou definir dois tablespaces separados, o primeiro já está em um SATA III Seagate Baracuda e outro que vou configurar no SSD (assim que o SSD chegar da loja). Nesse caso, o postgresql.conf deve ter duas configurações separadas (uma para o SSD e outra para o HDD). Existe alguma forma de fazer as configurações separadas por tablespace ou por banco de dados, sem precisar reiniciar o servidor e recarregar as configurações? Estou fazendo benchmarking para pesquisa científica e basicamente quero definir dois bancos de dados idênticos nos dois tablespaces e comparar o desempenho de leitura de consulta entre o SSD e o HDD para meus métodos.

Suponha que eu tenha a seguinte tabela de banco de dados com 3 campos inteiros.

A | B | C
1 | 2 | 3
1 | 2 | 4
1 | 3 | 1
2 | 4 | 2
2 | 4 | 3

Quando eu faço:

SELECT * FROM dbTable ORDER BY A,B LIMIT 1;

eu recebo

1 | 2 | 3

que é esperado. Mas o 2º registro:

 1 | 2 | 4

também tem os mesmos valores para dbFields A e B. Existe alguma maneira eficiente de realmente recuperar todos os registros que têm o mesmo valor que os registros top-k? Por exemplo, quando procuro os primeiros 100 registros para obter 102 registros, se os dois últimos tiverem os mesmos valores do 100º registro? Existe algum índice para acelerar tais consultas? Não me importo se tiver que ser feito com pl/pgsql (e não SQL simples) se a implementação for eficiente.

Com este comando, posso gerar 16384 números inteiros aleatórios entre 1 e 200.000.

SELECT generate_series (1,16384),(random()*200000)::int AS id

Eu quero gerar 10 conjuntos de tais números inteiros. Cada conjunto deve ter um identificador inteiro, algo assim:

1 | 135

1 | 1023

... fim do primeiro conjunto de 16384 números aleatórios

2 | 15672

2 | 258732

... fim do segundo conjunto de 16384 números aleatórios

Isso é possível com um comando SQL ou devo escrever uma função para isso?

Terei um enorme banco de dados PostgreSQL 9.3 com muitas tabelas com mais de 100M de entradas por tabela. Este banco de dados será basicamente somente leitura (uma vez que eu preencha todas as tabelas necessárias e construa os índices, não haverá mais operações de gravação no banco de dados) e acesso de usuário único (executar e fazer benchmark de várias consultas do localhost), pois o banco de dados será usado apenas para fins de pesquisa. As consultas sempre usarão JOIN em campos de banco de dados inteiros.

Provavelmente comprarei um SSD (256-512 GB) para esse fim. Eu não usei um SSD para um banco de dados antes, então há algo que eu deveria ter medo? Posso colocar o banco de dados inteiro no SSD ou apenas os índices? Existe algum conselho / tutorial específico necessário para ajustar o PostgreSQL para SSDs? Observe que eu tenho uma boa estação de trabalho com um i7 e 32 Gb de RAM, então talvez você possa oferecer alguns conselhos também.

Eu tenho uma lista enorme de matrizes inteiras (300.000.000 registros) armazenadas no banco de dados Postgres 9.2. Desejo pesquisar com eficiência esses registros para uma correspondência exata (somente igualdade). Já ouvi falar do módulo intarray e dos índices gist-gin correspondentes. Gostaria de fazer as seguintes perguntas:

• O PostgreSQL usa uma função hash para verificar a igualdade de arrays inteiros ou executa um algoritmo de força bruta comparando um a um os elementos do array?
• Se o PostgreSQL usa uma função hash, existe algum código de função PostgreSQL para realmente obter o resultado da função hash para uma matriz específica?
• Qual índice será melhor para tal tarefa? B-tree ou os índices gist-gin fornecidos pelo módulo intarray? O dataset será estático, ou seja, uma vez inseridos todos os registros não haverá mais inserções. Portanto, construir o índice/atualizar o tempo do índice não é importante para mim.

Eu tenho uma tabela de banco de dados com 3.000.000 linhas ou mais assim:

|ID|T4|T16|T64|TL

onde ID =INTEGER (PK) e T4,T16,T64,TL são campos de texto. Cada um é maior em comprimento que o anterior, ou seja, LENGTH(T4)

Desejo comparar uma linha desta tabela com um SELF JOIN com todas as outras linhas com base na distância levenshtein (extensão fuzzystrmatch) entre TL das linhas comparadas e obter os melhores resultados de k que correspondem à menor distância levenstein.

SELECT n1.*,n2.* 
FROM (SELECT * FROM dbtable WHERE ID =110) n1,
dbtable n2
ORDER BY levenshtein_less_equal(n1.TL,n2.TL,LENGTH(n1.TEXT)/4) LIMIT 100;

Claro que isso seria extremamente lento porque teria que fazer 3.000.000 cálculos de levenshtein.

Para agilizar as consultas, criei os "hashes" artificiais (campos T4,T16,T64). Então, eu quero verificar inicialmente a distância levenshtein entre os campos T4. Se esta distância entre T4 estiver dentro de algum limite, então verifico a distância levenshtein entre T16 e assim por diante. Dessa forma, em cada etapa, eu precisaria calcular menos distâncias levenshtein do que na abordagem ingênua.

A consulta aninhada é assim:

SELECT n5.*,levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
FROM
(SELECT n4.*
FROM
(SELECT n3.*
FROM
(SELECT 
n1.id AS n1_id,
n1.t4 AS n1_t4,
n1.t16 AS n1_t16,
n1.t64 AS n1_t64,
n1.tl AS n1_tl,

n2.id AS n2_id,
n2.t4 AS n2_t4,
n2.t16 AS n2_t16,
n2.t64 AS n2_t64,
n2.tl AS n2_tl
FROM (SELECT id,t4,t16,t64,tl FROM dbTable WHERE id=110) AS n1,dbTable AS n2
WHERE
n1.id<>n2.id
AND levenshtein_less_equal(n1.t4,n2.t4,LENGTH(n1.t4)) <= LENGTH(n1.t4)/2
) n3

WHERE levenshtein_less_equal(n3.n1_t16,n3.n2_t16,LENGTH(n3.n1_t16)/2) < LENGTH(n3.n1_t16)/2 
) AS n4

WHERE levenshtein_less_equal(n4.n1_t64,n4.n2_t64,LENGTH(n4.n1_t64)/2) < LENGTH(n4.n1_t64)/2)
AS n5
ORDER BY levenshtein_less_equal(n5.n1_tl,n5.n2_tl,LENGTH(n5.n1_tl)/4)
LIMIT 100;

A consulta é muito mais rápida que a ingênua original, pois precisa acessar apenas 112851 linhas para TL, mas:

O PLANO DE EXPLICAÇÃO:

Limit  (cost=872281.90..872282.15 rows=100 width=1168)
  ->  Sort  (cost=872281.90..872564.03 rows=112851 width=1168)
        Sort Key: (levenshtein_less_equal(dbTable.tl, n2.tl, (length(dbTable.tl) / 4)))
        ->  Nested Loop  (cost=0.00..867968.81 rows=112851 width=1168)
              Join Filter: ((dbTable.id <> n2.id) AND (levenshtein_less_equal(dbTable.t4, n2.t4, length(dbTable.t4)) <= (length(dbTable.t4) / 2)) AND (levenshtein_less_equal(dbTable.t16, n2.t16, (length(dbTable.t16) / 2)) < (length(dbTable.t16) / 2)) AND (levenshtein_less_equal(dbTable.t64, n2.t64, (length(dbTable.t64) / 2)) < (length(dbTable.t64) / 2)))
              ->  Index Scan using dbTable_pkey on dbTable  (cost=0.00..8.60 rows=1 width=584)
                    Index Cond: (id = 110)
              ->  Seq Scan on dbTable n2  (cost=0.00..699529.82 rows=3046982 width=584) 

Como você pode ver, o problema é que o otimizador de consulta junta/recolhe os cálculos de distância levenshtein entre T4, T16, T64, onde sem recolher seria provavelmente mais rápido, porque menos distâncias levenshtein precisariam ser feitas para T64. Eu usei SET from_collapse_limit=1; para evitar o colapso na mesma sessão, mas nada mudou. Existe uma maneira de impor essa funcionalidade hierárquica em uma consulta Postgres; Lembre-se de que nenhum dos T4...TL é indexado porque não acho que haja um índice para acelerar as distâncias levenshtein. Alguma sugestão para melhorar ainda mais o desempenho?

Desejo comparar o desempenho de uma consulta complicada específica JOIN para vários valores de ID de chave primária. A maioria das abordagens anteriores dizem para usar EXPLAIN ANALYZE para a consulta em questão e obter o "Total runtime: .... ms" para contar o desempenho. O problema é que, mesmo quando uso o mesmo ID, obtenho resultados muito diferentes, por exemplo, tempo variando de 20 segundos a 140 segundos. Note que cache não tem nada a ver com isso, pois ao rodar a mesma consulta em horários diferentes recebo esse tipo de variação, mesmo quando estamos falando do mesmo ID (obtenho sempre o mesmo plano de consulta).

Então minhas perguntas são:

1. O tempo de execução total depende do cliente/interface (por exemplo, pgAdminIII vs psql). Eu acho que psql dá valores mais consistentes
2. Depende do tempo da rede (devo rodar em localhost ou posso rodar até mesmo em uma máquina remota)
3. Existe uma maneira de fazer medições mais consistentes ou esse tipo de flutuação é considerado normal?

Estou usando o PostgreSQL 9.2.6 de 64 bits em uma máquina linux Ubuntu 12.10 de 64 bits com 2 GB de buffers compartilhados.