Como evitar condições na junção esquerda e otimizar a consulta?

Embora essa certamente seja uma consulta estranhamente escrita, usando o plano, podemos ver que há um índice ausente na conta de cobrança, sem ter que entender qual é o design da consulta geral.

Você não precisa verificar e rejeitar 7.649 linhas apenas para retornar 84, portanto, algumas das colunas no filtro devem fazer parte do índice. Não sabemos quão seletiva é cada parte do filtro, mas com base nos nomes e constantes das colunas, suponho que você precise de um índice de várias colunas sobre companyclientid e propertyid. (E provavelmente deve ter billingaccounttypeid e statusid nele também, não deve doer muito e pode ajudar)

Algumas considerações sobre a consulta. Se tivéssemos a estrutura e os índices das tabelas, seria mais útil (e o conselho talvez fosse diferente):

• A 2ª consulta não é equivalente à 1ª, portanto, não é incomum que você obtenha resultados diferentes. Então, vamos nos ater à primeira consulta:

    select count(*)
    from reservation as r
             JOIN companyclient as cc ON r.companyclientid = cc.companyclientid
             JOIN reservationitem as ri ON r.reservationid = ri.reservationid
             LEFT JOIN billingaccount as bi
                       ON (r.reservationid = bi.reservationid
                           AND cc.companyclientid = bi.companyclientid
                           AND bi.propertyid = '84'
                           AND NOT bi.isdeleted
                           AND bi.groupkey = bi.billingaccountid
                           AND bi.billingaccounttypeid = '3'
                           AND bi.reservationid IS NOT NULL
                           AND bi.statusid = '1')
    WHERE r.propertyid = '84'
      AND NOT r.isdeleted
      AND r.companyclientid is not null
      AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
      AND NOT ri.isdeleted
      AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);
  

• As condições r.companyclientid is not nulle bi.reservationid IS NOT NULLsão redundantes, pois essas colunas já participam das condições de associação e qualquer valor NULL não passará nas condições de associação. Portanto, esses dois podem ser removidos com segurança (mas ainda verifique se você obtém os mesmos resultados).

• A cc.companyclientid = bi.companyclientidcondição (dentro da subconsulta) pode ser substituída r.companyclientid = bi.companyclientiddesde que você tenha a condição de igualdade na consulta externa: r.companyclientid = cc.companyclientid. Isso não deixa nenhuma cccoluna na subconsulta e todas as condições da subconsulta envolvem apenas 2 tabelas, bie risso pode ajudar o planejador do Postgres a obter um plano mais simples (especialmente se houver índices adequados em billingaccounte reservation.

• Se e somente se o reservation. companyclientidnão for anulável e houver uma FOREIGN KEYrestrição que REFERENCES companyclient (companyclientid), podemos remover completamente o JOIN companyclient as cc ON r.companyclientid = cc.companyclientiddesde para qualquer linha em reservation, o estrangeiro garante que há uma única linha companycliente a contagem não seria afetada.

• usar count(1)em vez de count(*). Esta é apenas uma pequena melhoria em algumas versões.

Se todos os itens acima forem aplicados, a consulta se tornará
(e não se esqueça de comparar os resultados com a consulta correta original após cada modificação!) :

    select count(1)
    from reservation as r
             JOIN reservationitem as ri ON r.reservationid = ri.reservationid
             LEFT JOIN billingaccount as bi
                       ON (r.reservationid = bi.reservationid
                           AND r.companyclientid = bi.companyclientid
                           AND bi.propertyid = '84'
                           AND NOT bi.isdeleted
                           AND bi.groupkey = bi.billingaccountid
                           AND bi.billingaccounttypeid = '3'
                           AND bi.statusid = '1')
    WHERE r.propertyid = '84'
      AND NOT r.isdeleted
      AND ri.tenantid = '025aa64f-67fb-4c23-b975-2b0fc3f5d65a'
      AND NOT ri.isdeleted
      AND ri.reservationitemstatusid NOT IN (6, 3, 7, 8);

Agora vamos sugerir alguns índices, especificamente para esta consulta e um para cada tabela:

CREATE INDEX suggested_index_a ON
reservation
    (propertyid, reservationid, companyclientid)
WHERE (NOT isdeleted) ;

CREATE INDEX suggested_index_b ON
reservationitem
    (tenantid, reservationid) 
INCLUDE (reservationitemstatusid)
WHERE (NOT isdeleted) ;

CREATE INDEX suggested_index_c ON
billingaccount
    (propertyid, billingaccounttypeid, statusid,
     reservationid, companyclientid)
WHERE (groupkey = billingaccountid 
       AND NOT isdeleted) ;

Por favor, tente com os índices acima adicionados. Tenha em mente que qualquer índice consome espaço em disco (e espaço de memória quando usado), portanto, é uma troca.