Início / user-278464

user570286's questions

Martin Hope
user570286
Asked: 2024-05-08 05:07:23 +0800 CST
  • 7

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