Normalmente, eu não recomendo o uso de dicas de junção por todos os motivos padrão. Recentemente, no entanto, descobri um padrão em que quase sempre encontro uma junção de loop forçada para ter um desempenho melhor. Na verdade, estou começando a usar e recomendo tanto que queria uma segunda opinião para ter certeza de que não estou perdendo nada. Aqui está um cenário representativo (o código muito específico para gerar um exemplo está no final):

--Case 1: NO HINT
SELECT S.*
INTO #Results
FROM #Driver AS D
JOIN SampleTable AS S ON S.ID = D.ID

--Case 2: LOOP JOIN HINT
SELECT S.*
INTO #Results
FROM #Driver AS D
INNER LOOP JOIN SampleTable AS S ON S.ID = D.ID

SampleTable tem 1 milhão de linhas e seu PK é ID.
A tabela temporária #Driver tem apenas uma coluna, ID, nenhum índice e 50 mil linhas.

O que eu sempre encontro é o seguinte:

Caso 1: NO HINT
Index Scan na SampleTable
Hash Join
Maior duração (média de 333 ms)
Maior CPU (média de 331 ms)
Baixas leituras lógicas (4714)

Caso 2: LOOP JOIN HINT
Index Seek on SampleTable
Loop Join
Menor duração (média de 204ms, 39% menos)
CPU mais baixa (média de 206, 38% menos)
Leituras lógicas muito mais altas (160015, 34X mais)

A princípio, as leituras muito mais altas do segundo caso me assustaram um pouco porque diminuir as leituras costuma ser considerado uma medida decente de desempenho. Mas quanto mais penso sobre o que está realmente acontecendo, isso não me preocupa. Aqui está o meu pensamento:

SampleTable está contido em 4714 páginas, ocupando cerca de 36 MB. O caso 1 verifica todos eles, e é por isso que obtemos 4714 leituras. Além disso, ele deve executar 1 milhão de hashes, que consomem muita CPU e, em última análise, aumentam o tempo proporcionalmente. É todo esse hashing que parece aumentar o tempo no caso 1.

Agora considere o caso 2. Ele não está fazendo nenhum hash, mas em vez disso está fazendo 50.000 buscas separadas, que é o que está aumentando as leituras. Mas quão caras são as leituras comparativamente? Pode-se dizer que, se essas forem leituras físicas, pode ser muito caro. Mas lembre-se de 1) apenas a primeira leitura de uma determinada página pode ser física e 2) mesmo assim, o caso 1 teria o mesmo ou pior problema, pois é garantido que atingirá todas as páginas.

Levando em conta o fato de que ambos os casos precisam acessar cada página pelo menos uma vez, parece ser uma questão de qual é mais rápido, 1 milhão de hashes ou cerca de 155.000 leituras na memória? Meus testes parecem dizer o último, mas o SQL Server escolhe consistentemente o primeiro.

Pergunta

Então, de volta à minha pergunta: devo continuar forçando essa dica LOOP JOIN quando o teste mostra esses tipos de resultados ou estou perdendo alguma coisa na minha análise? Estou hesitante em ir contra o otimizador do SQL Server, mas parece que ele muda para usar uma junção de hash muito antes do que deveria em casos como esses.

Atualização 2014-04-28

Fiz mais alguns testes e descobri que os resultados que estava obtendo acima (em uma VM com 2 CPUs) não podiam ser replicados em outros ambientes (tentei em 2 máquinas físicas diferentes com 8 e 12 CPUs). O otimizador se saiu muito melhor nos últimos casos, a ponto de não haver esse problema pronunciado. Acho que a lição aprendida, que parece óbvia em retrospecto, é que o ambiente pode afetar significativamente o funcionamento do otimizador.

Planos de Execução

Plano de Execução Caso 1 Plano de Execução Caso 2

Código para gerar caso de exemplo

------------------------------------------------------------
-- 1. Create SampleTable with 1,000,000 rows
------------------------------------------------------------    

CREATE TABLE SampleTable
    (  
       ID         INT NOT NULL PRIMARY KEY CLUSTERED
     , Number1    INT NOT NULL
     , Number2    INT NOT NULL
     , Number3    INT NOT NULL
     , Number4    INT NOT NULL
     , Number5    INT NOT NULL
    )

--Add 1 million rows
;WITH  
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows  
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows  
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows 
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows 
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows 
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows 
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)
INSERT INTO SampleTable
SELECT Number, Number, Number, Number, Number, Number
FROM  FinalCte
WHERE Number <= 1000000

------------------------------------------------------------
-- Create 2 SPs that join from #Driver to SampleTable.
------------------------------------------------------------    
GO
IF OBJECT_ID('JoinTest_NoHint') IS NOT NULL DROP PROCEDURE JoinTest_NoHint
GO
CREATE PROC JoinTest_NoHint
AS
    SELECT S.*
    INTO #Results
    FROM #Driver AS D
    JOIN SampleTable AS S ON S.ID = D.ID
GO
IF OBJECT_ID('JoinTest_LoopHint') IS NOT NULL DROP PROCEDURE JoinTest_LoopHint
GO
CREATE PROC JoinTest_LoopHint
AS
    SELECT S.*
    INTO #Results
    FROM #Driver AS D
    INNER LOOP JOIN SampleTable AS S ON S.ID = D.ID
GO

------------------------------------------------------------
-- Create driver table with 50K rows
------------------------------------------------------------    
GO
IF OBJECT_ID('tempdb..#Driver') IS NOT NULL DROP TABLE #Driver
SELECT ID
INTO #Driver
FROM SampleTable
WHERE ID % 20 = 0

------------------------------------------------------------
-- Run each test and run Profiler
------------------------------------------------------------    

GO
/*Reg*/  EXEC JoinTest_NoHint
GO
/*Loop*/ EXEC JoinTest_LoopHint


------------------------------------------------------------
-- Results
------------------------------------------------------------    

/*

Duration CPU   Reads    TextData
315      313   4714     /*Reg*/  EXEC JoinTest_NoHint
309      296   4713     /*Reg*/  EXEC JoinTest_NoHint
327      329   4713     /*Reg*/  EXEC JoinTest_NoHint
398      406   4715     /*Reg*/  EXEC JoinTest_NoHint
316      312   4714     /*Reg*/  EXEC JoinTest_NoHint
217      219   160017   /*Loop*/ EXEC JoinTest_LoopHint
211      219   160014   /*Loop*/ EXEC JoinTest_LoopHint
217      219   160013   /*Loop*/ EXEC JoinTest_LoopHint
190      188   160013   /*Loop*/ EXEC JoinTest_LoopHint
187      187   160015   /*Loop*/ EXEC JoinTest_LoopHint

*/

Em uma tabela onde cada linha possui um contador (apenas um valor inteiro), preciso pegar o valor atual e aumentá-lo ao mesmo tempo .

Efetivamente, eu quero fazer isso:

SELECT counter FROM table WHERE id=123
UPDATE table SET counter=counter+1 WHERE id=123

Mas fazer isso como duas consultas obviamente não é seguro para threads: vários processos fazendo a mesma coisa (na mesma linha) podem obter o mesmo valor de contador. Preciso que todos sejam únicos, para que cada processo obtenha o valor atual real e o aumente em um.

Posso pensar em uma construção onde implemento um bloqueio manual por linha, mas gostaria de saber se existe uma maneira mais fácil de fazer isso?

Estou escrevendo um trabalho para transformar dados de um design antigo em um novo design. Nesse processo, preciso pegar o id de uma inserção em uma tabela separada e usá-lo em uma inserção na tabela de destino, da seguinte forma:

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

Eu tenho o SQL definido que corresponde ao seguinte formulário:

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

Eu queria que isso fosse executado SELECT * FROM inspara cada linha do SELECT.. mas, em vez disso, ele o executa apenas uma vez e usa esse valor para todas as linhas do SELECT. Como posso reestruturar meu SQL para obter o comportamento desejado?

edit4

t1 acaba se parecendo com:

1,<NULL>
(1 row)

t2 acaba se parecendo com:

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

O que eu quero que t1 pareça:

1,<NULL>
2,<NULL>
3,<NULL>
.
.

O que eu quero que t2 se pareça:

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

editar Para abordar o que a_horse_with_no_name disse, eu também tentei isso (com o mesmo resultado):

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

edit2 Acabei de tentar referenciar diretamente o apropriado SEQUENCEem minha consulta, e isso FUNCIONA - mas não gosto muito dessa solução (principalmente porque não gosto de nomes de objeto codificados). do que referenciar diretamente o nome do SEQUENCE, eu agradeceria. :)

edit3 Suponho que outra solução seria usar um PROCEDUREpara fazer o INSERTem vez de um CTE .. mas ainda apreciaria as opções/sugestões.

Estou tentando determinar quais índices usar para uma consulta SQL com uma WHEREcondição e uma GROUP BYque está sendo executada muito lentamente.

Minha consulta:

SELECT group_id
FROM counter
WHERE ts between timestamp '2014-03-02 00:00:00.0' and timestamp '2014-03-05 12:00:00.0'
GROUP BY group_id

A tabela atualmente possui 32.000.000 linhas. O tempo de execução da query aumenta muito quando eu aumento o time-frame.

A tabela em questão fica assim:

CREATE TABLE counter (
    id bigserial PRIMARY KEY
  , ts timestamp NOT NULL
  , group_id bigint NOT NULL
);

Atualmente tenho os seguintes índices, mas o desempenho ainda é lento:

CREATE INDEX ts_index
  ON counter
  USING btree
  (ts);

CREATE INDEX group_id_index
  ON counter
  USING btree
  (group_id);

CREATE INDEX comp_1_index
  ON counter
  USING btree
  (ts, group_id);

CREATE INDEX comp_2_index
  ON counter
  USING btree
  (group_id, ts);

A execução de EXPLAIN na consulta fornece o seguinte resultado:

"QUERY PLAN"
"HashAggregate  (cost=467958.16..467958.17 rows=1 width=4)"
"  ->  Index Scan using ts_index on counter  (cost=0.56..467470.93 rows=194892 width=4)"
"        Index Cond: ((ts >= '2014-02-26 00:00:00'::timestamp without time zone) AND (ts <= '2014-02-27 23:59:00'::timestamp without time zone))"

SQL Fiddle com dados de exemplo: http://sqlfiddle.com/#!15/7492b/1

A questão

O desempenho desta consulta pode ser melhorado adicionando índices melhores ou devo aumentar o poder de processamento?

Editar 1

PostgreSQL versão 9.3.2 é usado.

Editar 2

Eu tentei a proposta de @Erwin com EXISTS:

SELECT group_id
FROM   groups g
WHERE  EXISTS (
   SELECT 1
   FROM   counter c
   WHERE  c.group_id = g.group_id
   AND    ts BETWEEN timestamp '2014-03-02 00:00:00'
                 AND timestamp '2014-03-05 12:00:00'
   );

Mas infelizmente isso não pareceu aumentar o desempenho. O plano de consulta:

"QUERY PLAN"
"Nested Loop Semi Join  (cost=1607.18..371680.60 rows=113 width=4)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Bitmap Heap Scan on counter c  (cost=1607.18..158895.53 rows=60641 width=4)"
"        Recheck Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        ->  Bitmap Index Scan on comp_2_index  (cost=0.00..1592.02 rows=60641 width=0)"
"              Index Cond: ((group_id = g.id) AND (ts >= '2014-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Editar 3

O plano de consulta para a consulta LATERAL do ypercube:

"QUERY PLAN"
"Nested Loop  (cost=8.98..1200.42 rows=133 width=20)"
"  ->  Seq Scan on groups g  (cost=0.00..2.33 rows=133 width=4)"
"  ->  Result  (cost=8.98..8.99 rows=1 width=0)"
"        One-Time Filter: ($1 IS NOT NULL)"
"        InitPlan 1 (returns $1)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan using comp_2_index on counter c  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"
"        InitPlan 2 (returns $2)"
"          ->  Limit  (cost=0.56..4.49 rows=1 width=8)"
"                ->  Index Only Scan Backward using comp_2_index on counter c_1  (cost=0.56..1098691.21 rows=279808 width=8)"
"                      Index Cond: ((group_id = $0) AND (ts IS NOT NULL) AND (ts >= '2010-03-02 00:00:00'::timestamp without time zone) AND (ts <= '2014-03-05 12:00:00'::timestamp without time zone))"

Eu tenho table1e table2no MySQL. auto_incrementAmbos têm uma chave primária id.

Se os esquemas de tabela corresponderem e eu fizer INSERT INTO table1 (SELECT * FROM table2)o que acontece com as novas linhas inseridas em table1? Eles mantêm seus idvalores antigos e geram conflitos quando uma linha de table1tem o mesmo id? Os novos valores são gerados pelo auto_increment? Depende do mecanismo de armazenamento ou bloqueio?

É uma maneira postgres de combinar IS DISTINCT FROMou ANYalguma outra maneira legal de obter o mesmo resultado?

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <> any(array[null, 'A']);

 count
-------
     1
(1 row)

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo is distinct from any(array[null, 'A']);  

ERROR:  syntax error at or near "any"
LINE 3: where foo is distinct from any(array[null, 'A']);
                                   ^

Tive alguns problemas ao modelar um esquema elétrico em SQL. A estrutura que eu gostaria de capturar é

  part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

onde "inst" é a abreviação de "instância".

Por exemplo, eu poderia ter um partamplificador operacional LM358 com pins 1OUT, 1IN-, 1IN+, GND, 2IN+, 2IN-, 2OUT e V _CC . Eu poderia então colocar esta parte em um esquema, criando um part_inste 8 pin_insts.

Ignorando os campos de dados, minha tentativa inicial de um esquema foi

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

O principal problema com esse esquema é que a pin_instpode estar vinculado a part_instwith , part_id=1mas pintem part_id=2.

Eu gostaria de evitar esse problema no nível do banco de dados, e não no nível do aplicativo. Então, modifiquei minhas chaves primárias para impor isso. Marquei as linhas alteradas com --.

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial,                                          --
    part_id bigint not null references parts,
    primary key (pin_id, part_id)                              --
);
create table part_insts (
    part_inst_id bigserial,                                    --
    part_id bigint not null references parts,
    primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,                              --
    pin_id bigint not null,                                    --
    part_id bigint not null references parts,                  --
    foreign key (part_inst_id, part_id) references part_insts, --
    foreign key (pin_id, part_id) references pins              --
);

Minha reclamação com esse método é que ele polui as chaves primárias: em todos os lugares em que me refiro a um part_inst, preciso acompanhar tanto o part_inst_idquanto o part_id. Existe outra maneira de aplicar a restrição pin_inst.part_inst.part_id = pin_inst.pin.part_idsem ser excessivamente prolixo?

Considerando que um índice não clusterizado é baseado no índice clusterizado, seria necessário que o índice não clusterizado listasse alguma das colunas contidas no índice clusterizado?

Em outras palavras, se a tabela Produtos contiver um índice clusterizado em ProductID, ao criar um índice não clusterizado onde seria aconselhável incluir a coluna ProductID, é necessário adicioná-lo como uma coluna?

Caso contrário, há cenários em que seria bom adicionar o nome da coluna ao índice não clusterizado?

Estou trabalhando na transferência de um grande aplicativo baseado na web pl/sql para o servidor dedicado. Este aplicativo está localizado em um esquema com 70 pacotes de código de programa. Esta aplicação foi feita aproximadamente cerca de 15 pessoas em momentos diferentes. E era uma prática normal para nós criar chaves estrangeiras nas tabelas de referência em esquemas diferentes porque é realmente conveniente e mantém o banco de dados muito limpo, porque não precisamos manter as mesmas tabelas de referência em esquemas diferentes.

Mas de qualquer maneira meu DBA (que criou uma nova instância com DB e copiou meu aplicativo dentro da zona do Solaris) disse muito duramente hoje: "Chaves estrangeiras em diferentes esquemas são más e você precisa destruí-las!". Ele não explicou seu ponto de vista.

É realmente uma má ideia fazer isso com aplicativos grandes?

Quais são as desvantagens de usar o Galera Cluster em vez da replicação mestre/escravo regular? O tempo de atraso do escravo 0 do Galera, a replicação síncrona e nenhum ponto único de falha parecem muito atraentes, então por que o cluster do Galera não é tão comum?