Linhas consistentes

A questão importante que ainda não parece estar no seu radar:
De cada conjunto de linhas para o mesmo seriesName, você quer as colunas de uma linha ou apenas quaisquer valores de várias linhas (que podem ou não vir da mesma linha )?

Sua resposta faz o último, você combina o máximo dbidcom o máximo retreivaltime, que pode vir de uma linha diferente.

Para obter linhas consistentesDISTINCT ON , use e envolva-as em uma subconsulta para ordenar o resultado de maneira diferente:

SELECT * FROM (
   SELECT DISTINCT ON (seriesName)
          dbid, seriesName, retreivaltime
   FROM   FileItems
   WHERE  sourceSite = 'mk' 
   ORDER  BY seriesName, retreivaltime DESC NULLS LAST  -- latest retreivaltime
   ) sub
ORDER BY retreivaltime DESC NULLS LAST
LIMIT  100;

Detalhes para DISTINCT ON:

• Selecione a primeira linha em cada grupo GROUP BY?

A parte: provavelmente deveria ser retrievalTime, ou melhor ainda: retrieval_time. Identificadores de maiúsculas e minúsculas sem aspas são uma fonte comum de confusão no Postgres.

Melhor desempenho com rCTE

Como estamos lidando com uma grande tabela aqui, precisaríamos de uma consulta que possa usar um índice, o que não é o caso da consulta acima (exceto WHERE sourceSite = 'mk')

Em uma inspeção mais detalhada, seu problema parece ser um caso especial de uma varredura de índice solto . O Postgres não suporta varreduras de índice solto nativamente, mas pode ser emulado com um CTE recursivo . Há um exemplo de código para o caso simples no Postgres Wiki.

Ver:

• SELECT DISTINCT é mais lento que o esperado na minha tabela no PostgreSQL
• Otimize a consulta GROUP BY para recuperar o registro mais recente por usuário

Mas seu caso é mais complexo. Acho que encontrei uma variante para fazê-lo funcionar para você. Com base neste índice (sem WHERE sourceSite = 'mk')

CREATE INDEX mi_special_full_idx ON MangaItems
(retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST, dbid)

Ou (com WHERE sourceSite = 'mk')

CREATE INDEX mi_special_granulated_idx ON MangaItems
(sourceSite, retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST, dbid)

O primeiro índice pode ser usado para ambas as consultas, mas não é totalmente eficiente com a WHEREcondição adicional. O segundo índice é de uso muito limitado para a primeira consulta. Como você tem as duas variantes da consulta, considere criar os dois índices.

Eu adicionei dbidno final para permitir varreduras somente de índice .

Essa consulta com um CTE recursivo faz uso do índice. Testei com o Postgres 9.3 e funcionou para mim: sem varredura sequencial, todas as varreduras somente de índice :

WITH RECURSIVE cte AS (
   (
   SELECT dbid, seriesName, retreivaltime, 1 AS rn, ARRAY[seriesName] AS arr
   FROM   MangaItems
   WHERE  sourceSite = 'mk'
   ORDER  BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT i.dbid, i.seriesName, i.retreivaltime, c.rn + 1, c.arr || i.seriesName
   FROM   cte c
   ,      LATERAL (
      SELECT dbid, seriesName, retreivaltime
      FROM   MangaItems
      WHERE (retreivaltime, seriesName) < (c.retreivaltime, c.seriesName)
      AND    sourceSite = 'mk'  -- repeat condition!
      AND    seriesName <> ALL(c.arr)
      ORDER  BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST
      LIMIT  1
      ) i
   WHERE  c.rn < 101
   )
SELECT dbid
FROM   cte
ORDER  BY rn;

Você precisa incluir seriesName em ORDER BY, pois retreivaltimenão é exclusivo. "Quase" único ainda não é único.

Explicação

• A consulta não recursiva começa com a última linha.

• A consulta recursiva adiciona a próxima linha mais recente com uma seriesNameque ainda não está na lista, etc., até que tenhamos 100 linhas.

• As partes essenciais são a JOINcondição (b.retreivaltime, b.seriesName) < (c.retreivaltime, c.seriesName)e a ORDER BYcláusula ORDER BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST. Ambos correspondem à ordem de classificação do índice, o que permite que a mágica aconteça.

• Coletando seriesNameem uma matriz para descartar duplicatas. O custo para b.seriesName <> ALL(c.foo_arr)cresce progressivamente com o número de linhas, mas para apenas 100 linhas ainda é barato.

• Apenas retornando dbidconforme esclarecido nos comentários.

Alternativa com índices parciais:

Já lidamos com problemas semelhantes antes. Aqui está uma solução completa altamente otimizada baseada em índices parciais e uma função de loop:

• O índice espacial pode ajudar uma consulta "intervalo - ordem por - limite"

Provavelmente o caminho mais rápido (exceto para uma visão materializada) se feito corretamente. Mas mais complexo.

Visualização Materializada

Como você não tem muitas operações de gravação e elas não são críticas ao desempenho, conforme indicado nos comentários (deve estar na pergunta), salve as n primeiras linhas pré-computadas em uma visualização materializada e atualize-a após alterações relevantes no tabela subjacente. Em vez disso, baseie suas consultas críticas de desempenho na visualização materializada.

• Poderia ser apenas um mv "fino" dos últimos 1000 dbidou mais. Na consulta, junte-se à tabela original. Por exemplo, se o conteúdo às vezes é atualizado, mas as n primeiras linhas podem permanecer inalteradas.

• Ou um mv "gordo" com linhas inteiras para retornar. Mais rápido, ainda. Precisa ser atualizado com mais frequência, obviamente.

Detalhes no manual aqui e aqui .

Ok, eu li os documentos mais e agora entendo o problema pelo menos um pouco melhor.

Basicamente, o que está acontecendo é que existem vários valores possíveis dbidcomo resultado da GROUP BY seriesNameagregação. Com SQLite e MySQL, aparentemente o mecanismo de banco de dados apenas escolhe um aleatoriamente (o que é absolutamente bom no meu aplicativo).

No entanto, o PostgreSQL é muito mais conservador, então, em vez de escolher um valor aleatório, ele gera um erro.

Uma maneira simples de fazer essa consulta funcionar é aplicar uma função de agregação ao valor relevante:

SELECT MAX(dbid) AS mdbid, seriesName, MAX(retreivaltime) AS mrt
    FROM MangaItems 
    WHERE sourceSite='mk' 
    GROUP BY seriesName
    ORDER BY mrt DESC 
    LIMIT 100 
    OFFSET 0;

Isso torna a saída da consulta totalmente qualificada e a consulta agora funciona.

Bem, na verdade acabei usando alguma lógica procedural fora do banco de dados para realizar o que queria fazer.

Basicamente, 99% do tempo, eu quero os últimos 100.200 resultados. O planejador de consultas parece não otimizar para isso e, se o valor de OFFSETfor grande, meu filtro procedural será muito mais lento.

De qualquer forma, usei um cursor nomeado para iterar manualmente as linhas no banco de dados, recuperando as linhas em grupos de algumas centenas. Em seguida, filtrei-os para distinção no código do meu aplicativo e fecho o cursor imediatamente depois de acumular o número de resultados distintos desejados.

O makocódigo (basicamente python). Muitas instruções de depuração restantes.

<%def name="fetchMangaItems(flags='', limit=100, offset=0, distinct=False, tableKey=None, seriesName=None)">
    <%
        if distinct and seriesName:
            raise ValueError("Cannot filter for distinct on a single series!")

        if flags:
            raise ValueError("TODO: Implement flag filtering!")

        whereStr, queryAdditionalArgs = buildWhereQuery(tableKey, None, seriesName=seriesName)
        params = tuple(queryAdditionalArgs)


        anonCur = sqlCon.cursor()
        anonCur.execute("BEGIN;")

        cur = sqlCon.cursor(name='test-cursor-1')
        cur.arraysize = 250
        query = '''

            SELECT
                    dbId,
                    dlState,
                    sourceSite,
                    sourceUrl,
                    retreivalTime,
                    sourceId,
                    seriesName,
                    fileName,
                    originName,
                    downloadPath,
                    flags,
                    tags,
                    note

            FROM MangaItems
            {query}
            ORDER BY retreivalTime DESC;'''.format(query=whereStr)

        start = time.time()
        print("time", start)
        print("Query = ", query)
        print("params = ", params)
        print("tableKey = ", tableKey)

        ret = cur.execute(query, params)
        print("Cursor ret = ", ret)
        # for item in cur:
        #   print("Row", item)

        seenItems = []
        rowsBuf = cur.fetchmany()

        rowsRead = 0

        while len(seenItems) < offset:
            if not rowsBuf:
                rowsBuf = cur.fetchmany()
            row = rowsBuf.pop(0)
            rowsRead += 1
            if row[6] not in seenItems or not distinct:
                seenItems.append(row[6])

        retRows = []

        while len(seenItems) < offset+limit:
            if not rowsBuf:
                rowsBuf = cur.fetchmany()
            row = rowsBuf.pop(0)
            rowsRead += 1
            if row[6] not in seenItems or not distinct:
                retRows.append(row)
                seenItems.append(row[6])

        cur.close()
        anonCur.execute("COMMIT;")

        print("duration", time.time()-start)
        print("Rows used", rowsRead)
        print("Query complete!")

        return retRows
    %>

</%def>

Atualmente, isso recupera os 100.200 itens de série distintos mais recentes em 115 ~ 80 milissegundos (o tempo mais baixo é ao usar uma conexão local, em vez de um soquete TCP), enquanto processa aproximadamente 1.500 linhas.

Venha comentários:

• As linhas são lidas em blocos de 250.
• buildWhereQuery is my own dynamic query builder. Yes, this is a horrible idea. Yes, I know about SQLalchemy et al. I wrote my own because A. this is a personal project that I don't expect to ever use outside of my home LAN, and B. It's a great way to learn SQL.
• I may consider switching between the two query mechanisms as dependent on the value of offset. It looks like when offset > 1000 and I'm filtering for distinct items, this approach starts to exceed the time required for procedures like the ones in @ErwinBrandstetter's answer.
• @ErwinBrandstetter's answer is still a much better general solution. This is only better in one very specific case.
• I had to use two cursors, for some odd reason. You can't create a named cursor unless you're in a transaction, but you can't start a transaction without a cursor (note - this is with autocommit mode off). I have to instantiate a anonymous cursor, issue some SQL (just a BEGIN, here), create my named cursor, use it, close it, and finally commit with the anonymous cursor.
• This could probably be done entirely in PL/pgSQL, and the result would probably be even faster, but I know python much better.