A eficácia do índice composto onde o prefixo é contínuo/alta cardinalidade

Eu diria que parte do seu raciocínio está correto e que pensar nas consequências da estrutura do índice é de fato extremamente importante. Como sempre, @Wernfried Domscheit destaca alguns pontos importantes em sua resposta . Mas acho que há mais para desvendar aqui, então vamos nos aprofundar. Começaremos respondendo diretamente a algumas das afirmações.

Tenho certeza que o índice composto será utilizado nesta consulta, para que a classificação seja eficiente.

O índice pode ser usado, sim. Mas, como mencionei antes , isso não significa que será usado. Isto é particularmente verdadeiro na presença de outros índices viáveis, dos quais falaremos mais tarde.

... podemos assumir que time_utcé único em toda a coleção.

Suponhamos que haja 1 milhão de documentos no acervo e apenas 10 serão devolvidos. Suponhamos ainda que os 10 documentos estejam localizados na parte inferior da time_utcclassificação.

Neste cenário, a consulta deve varrer todo o valor de 1 milhão do índice para descobrir os 10 documentos.

Certo. O comportamento/problema que você descreveu não está isolado no local onde os resultados estão localizados durante a verificação do índice.

No cenário que você descreveu, a varredura de índice necessária é muito 'ampla'. Isso o torna bastante ineficiente no atendimento da consulta. A colocação dos resultados nesse índice é irrelevante para o comportamento e desempenho da consulta em geral. Sua consulta levaria o mesmo tempo e faria a mesma quantidade de trabalho se esses 10 documentos também fossem os primeiros 10 na digitalização do índice.

Podemos ver isso visualizando as estatísticas de execução do explainplano ao emitir a classificação nas duas direções, uma vez que o índice pode ser verificado na direção forwardou . reverseUsando a classificação decrescente fornecida na pergunta:

> db.foo.aggregate([ { $match: { time_utc: { $exists: true }, city: "new york", age: { $gt: 18 } } }, { $sort: { time_utc: -1 } }]).explain("executionStats").executionStats
{
  nReturned: 10,
  totalKeysExamined: 1000000,
  totalDocsExamined: 10,
  ...
      keyPattern: { time_utc: 1, city: 1, age: 1 },
      direction: 'backward',

E usando o oposto (ascendente):

> db.foo.aggregate([ { $match: { time_utc: { $exists: true }, city: "new york", age: { $gt: 18 } } }, { $sort: { time_utc: 1 } }]).explain("executionStats").executionStats
{
  nReturned: 10,
  totalKeysExamined: 1000000,
  totalDocsExamined: 10,
  ...
      keyPattern: { time_utc: 1, city: 1, age: 1 },
      direction: 'forward',

Vamos supor o contrário, que existam apenas 2 valores únicos em time_utc. Neste cenário, parece que o índice para os campos citye agepode de fato ser eficaz.

Sim. Como você deixou implícito aqui, isso é uma consequência da distribuição dos dados, em oposição a algum comportamento diferente do banco de dados nesta situação.

Usando a terminologia acima, os limites para a varredura do índice ainda são tão “amplos” quanto eram antes. A diferença aqui é que a ‘largura’ do índice é muito menor. Nesta situação em que existem 2 valores únicos, esperaria explainque a saída (provavelmente) reportasse apenas alguns, seekso que refletiria a estrutura lógica do índice sendo 'mais estreita' a este respeito.

Vamos explorar isso um pouco mais.

Varredura 'ampla'

Em geral, quando a definição do índice leva ao(s) campo(s) de classificação, a varredura do índice nesse campo não será limitada. No seu caso, você tem uma condição predicada no mesmo campo ( time_utc), mas na verdade isso não resulta no estreitamento da varredura. Podemos ver isso na explainsaída:

> db.foo.aggregate([ { $match: { time_utc: { $exists: true }, city: "new york", age: { $gt: 18 } } }, { $sort: { time_utc: -1 } }]).explain().queryPlanner.winningPlan.inputStage.indexBounds
{
  time_utc: [ '[MaxKey, MinKey]' ],
  city: [ '["new york", "new york"]' ],
  age: [ '[inf.0, 18)' ]
}

Esses limites são os mesmos, independentemente dos dados da coleção.

Agora, se olharmos as estatísticas de execução do IXSCANconjunto de dados original (1 milhão de valores únicos), podemos ver que o banco de dados precisa percorrer o índice para encontrar a seção relevante e acaba olhando para tudo:

> db.foo.aggregate([{$group:{_id:'$time_utc'}},{$count:'uniqueTimes'}])
[ { uniqueTimes: 1000000 } ]
> db.foo.aggregate([ { $match: { time_utc: { $exists: true }, city: "new york", age: { $gt: 18 } } }, { $sort: { time_utc: -1 } }]).explain("executionStats").executionStats.executionStages.inputStage
{
  keyPattern: { time_utc: 1, city: 1, age: 1 },
  keysExamined: 1000000,
  seeks: 999991,
  ...

Mas quando há apenas 2 valores distintos para a chave principal definida pelo índice, o banco de dados consegue ignorar a maior parte dela:

> db.foo.aggregate([{$group:{_id:'$time_utc'}},{$count:'uniqueTimes'}])
[ { uniqueTimes: 2 } ]
> db.foo.aggregate([ { $match: { time_utc: { $exists: true }, city: "new york", age: { $gt: 18 } } }, { $sort: { time_utc: -1 } }]).explain("executionStats").executionStats.executionStages.inputStage
{
  keyPattern: { time_utc: 1, city: 1, age: 1 },
  keysExamined: 12,
  seeks: 3,
  ...

Recomendações (Orientação ESR)

Como a outra resposta menciona, se os conjuntos de resultados forem pequenos, o custo da classificação não será particularmente alto.

Em geral, porém, o MongoDB sugere que o ponto de partida para a indexação é o que eles chamam de Regra ESR . Usando esta abordagem, o predicado on cityé uma condição de igualdade, portanto deve ser colocado em primeiro lugar no índice. O time_utcpredicado, entretanto, é uma condição de intervalo , pois basicamente significa "qualquer valor, exceto o ausente". Como esse campo é usado em ambos os aspectos (classificação e intervalo), o índice que as diretrizes sugerem que você tente é:

{
  city: 1,
  time_utc: 1,
  age: 1
}

Agora, voltando ao ponto sobre o tamanho do conjunto de resultados, é definitivamente possível que você possa realmente ver um melhor desempenho trocando a segunda e a terceira chaves e incorrendo na classificação de bloqueio:

{
  city: 1,
  age: 1,
  time_utc: 1
}

Isso depende inteiramente da seletividade dos dados e pode ser que alguns valores de predicado funcionem melhor com um índice do que com outro. Você teria que testar e avaliar em seu próprio ambiente.

Primeiro, os valores de data/hora devem ser melhor armazenados adequadamente como Dateobjetos, não como números ou - pior ainda - como strings. Dateos objetos são sempre horários UTC, portanto, devem atender aos seus requisitos.

Se a condição on citye ageretornar apenas 10 documentos, você não precisará de um índice em time_utc. Para ordenação de 10 documentos não importa se utiliza índice ou não, independentemente de time_utcpossuir 10 milhões de valores distintos ou apenas dois valores distintos.