Início / dba / Perguntas / 339327
Accepted
user570286
Asked: 2024-05-08 05:07:23 +0800 CST

Como posso encontrar a linha superior e contá-las com apenas uma varredura?

  • 772

Suponha que eu tenha um esquema como o seguinte:

-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);

-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
  SELECT i, c1, c2 FROM t1 WHERE c1 % 2 == 0;
-- The real WHERE clause is slightly more complex.

Suponha que essa tabela t1contenha alguns milhões de linhas:

INSERT INTO t1(i, c1, c2)
  SELECT value, random(), random() FROM generate_series(1, 5000000);

Suponha que eu queira obter o índice da linha t1que tem o par mais alto c1e a contagem de linhas com par c1que também tem par c2:

SELECT
  (SELECT i FROM t1_filtered ORDER BY c1 DESC LIMIT 1),
  (SELECT count(*) FROM t1_filtered WHERE c2 % 2 == 0);

A cláusula real ORDER BYé muito mais complexa, mas isto é suficiente para ilustrar o meu problema.

Parece-me que isso deveria ser possível com apenas uma varredura t1, mas EXPLAIN QUERY PLANdiz que esta consulta verifica t1duas vezes:

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCALAR SUBQUERY 2
   `--SCAN t1

Se eu unir as duas subconsultas em vez de escrevê-las como colunas de resultados, o plano de consulta será diferente, mas ainda terá duas varreduras de t1:

QUERY PLAN
|--CO-ROUTINE (subquery-1)
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
|--MATERIALIZE (subquery-2)
|  `--SCAN t1
|--SCAN (subquery-1)
`--SCAN (subquery-2)

Imperativamente, eu esperaria que esta consulta fosse algo como este pseudocódigo:

var top_row = {i: NULL, c1: 0};
var count = 0;
for each {i, c1, c2} in t1:
  if c1 % 2 == 0:
    if c1 > top_row.c1:
      top_row = {i, c1};
    if c2 % 2 == 0:
      count = count + 1;
return {top_row.i, count};

Como posso fazer com que o planejador de consultas veja que isso precisa apenas de uma verificação?

Atualização, 09/05/2024: tentei a consulta proposta por Charlieface . De acordo com EXPLAIN QUERY PLAN, isso faz com que o planejador de consultas use apenas uma varredura de t1...

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  |--SCAN t1
|  |  `--USE TEMP B-TREE FOR ORDER BY
|  `--SCAN (subquery-4)
`--SCAN t

... mas é executado significativamente mais lentamente do que minha SELECT (...), (...)consulta original: testando ambas as consultas no SQLite REPL com .timer on, descobri que (para minha tabela de exemplo t1com 5 milhões de linhas de dados aleatórios) minha consulta original tem um tempo médio de execução de 2,22 segundos com desvio padrão amostral de 0,03 s e esta consulta proposta tem média de 5,57 s com st. dev. 0,23 seg.

Algo que minha intuição imperativa não me sugeriu, mas que o estudo dos planos de consulta "USE TEMP B-TREE FOR ORDER BY" sugeriu foi adicionar um índice em t1(c1). Isso realmente acelera minha consulta original, fazendo com que a média seja de 1,09 s com st. dev. 0,02 seg. No entanto, para minha surpresa, o índice aparentemente faz com que a consulta de Charlieface demore muito mais ou possivelmente até mesmo a torna ininterrupta - interrompi-a depois de esperar 108 segundos e interrompi-a depois de tentar novamente por 32 s, e então o fiz. não tente novamente.

A consulta de Charlieface responde literalmente à minha pergunta, reduzindo o número de varreduras t1para um, mas seu baixo desempenho prático em relação à minha consulta original me faz relutante em aceitá-la como resposta. Espero que isso não seja "mover demais as traves da baliza". Eu marquei isso como query-performance, então o desempenho fez parte da minha pergunta desde o início.

Atualização, 09/05/2024 nº 2: Com CREATE INDEX t1_c1 ON t1(c1), o plano de consulta para minha consulta original tornou-se

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX t1_c1
`--SCALAR SUBQUERY 2
   `--SCAN t1

e o plano de consulta para a consulta de Charlieface tornou-se

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX t1_c1
|  `--SCAN (subquery-4)
`--SCAN t

Com CREATE INDEX index_per_comment660077_339327 ON t1 (c1 DESC) WHERE (c1 % 2 = 0)o comentário do Charlieface (SQLite não suporta INCLUDE), os planos de consulta tornam-se, respectivamente,

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX index_per_comment660077_339327
`--SCALAR SUBQUERY 2
   `--SCAN t1 USING INDEX index_per_comment660077_339327

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX index_per_comment660077_339327
|  `--SCAN (subquery-4)
`--SCAN t
query-performance

2 respostas

  1. Best Answer

    Com SQLite, se uma consulta agregada contém uma única função min() ou max(), então os valores das colunas usadas na saída são retirados da linha onde o valor min() ou max() foi alcançado. É um recurso .

    SELECT i, MAX(c1), COUNT(*) FILTER (WHERE c2 % 2 = 0) 
FROM t1_filtered;

    A consulta não precisa de um índice e possui este plano de consulta simples:

    EXPLAIN QUERY PLAN
SELECT i, MAX(c1), COUNT(*) FILTER (WHERE c2 % 2 = 0) 
FROM t1_filtered;

QUERY PLAN
`--SCAN t1

    A consulta irá computar MAX(c1)e COUNT(*)em uma única varredura e retornará o valor ique foi encontrado na mesma linha onde MAX(c1)foi alcançado.

    Um EXPLAIN mostrará a consulta compilada em OPcodes SQLite e você poderá ver que o código gerado é exatamente o mesmo pseudocódigo que você desejava.

    EXPLAIN
SELECT i, MAX(c1), COUNT(*) FILTER (WHERE c2 % 2 = 0) 
FROM t1_filtered;

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     23    0                    0   Start at 23
1     Null           0     1     5                    0   r[1..5]=NULL
2     OpenRead       1     2     0     3              0   root=2 iDb=0; t1
3     Rewind         1     17    0                    0
4       Column         1     1     7                    0   r[7]= cursor 1 column 1
5       Remainder      8     7     6                    0   r[6]=r[7]%r[8]
6       Ne             9     16    6                    80  if r[6]!=r[9] goto 16
7       Column         1     1     6                    0   r[6]= cursor 1 column 1
8       CollSeq        10    0     0     BINARY-8       0
9       AggStep        0     6     4     max(1)         1   accum=r[4] step(r[6])
10      Column         1     2     7                    0   r[7]= cursor 1 column 2
11      Remainder      8     7     6                    0   r[6]=r[7]%r[8]
12      Ne             9     14    6                    80  if r[6]!=r[9] goto 14
13      AggStep        0     0     5     count(0)       0   accum=r[5] step(r[0])
14      If             10    16    0                    0
15      Rowid          1     1     0                    0   r[1]=t1.rowid
16    Next           1     4     0                    1
17    AggFinal       4     1     0     max(1)         0   accum=r[4] N=1
18    AggFinal       5     0     0     count(0)       0   accum=r[5] N=0
19    Copy           1     11    0                    0   r[11]=r[1]
20    Copy           4     12    1                    0   r[12..13]=r[4..5]
21    ResultRow      11    3     0                    0   output=r[11..13]
22    Halt           0     0     0                    0
23    Transaction    0     0     2     0              1   usesStmtJournal=0
24    Integer        2     8     0                    0   r[8]=2
25    Integer        0     9     0                    0   r[9]=0
26    Goto           0     1     0                    0
    • 1

  2. Você pode usar numeração de linhas e agregação condicional

    SELECT
  MIN(t.i) FILTER (WHERE t.rn = 1),
  COUNT(*) FILTER (WHERE t.c2 % 2 = 0)
FROM (
    SELECT *,
      ROW_NUMBER() OVER (ORDER BY t.c1 DESC) AS rn
    FROM t1_filtered t
) t;
    • 0

relate perguntas