query optimization that adds values according to a clause

Question

Hi.

I have a query that I must bring two columns, and one of the columns will return the value of a column, plus the sum of another column respecting the Where clause to add the values.

I came to find the result that suits me as follows:

select a.entcodent as codent, a.asscodfpg as codfpg, a.asscodass as codigo, a.assmatass as matricula, a.assnomass as nome, a.asslogema as email, case a.assnivass when '0' then 'P' when '1' then 'O' END as nivel, f.fpgrazsoc as fonte_pagadora, a.asslimcre - coalesce( (select sum(movvalpri) as consumo from car550 inner join car400 on car550.lojcodloj = car400.lojcodloj and car550.entcodent = car400.entcodent and (car400.vintipvin = 1 or car400.lojcodloj = 9020) where car550.entcodent = a.entcodent and car550.asscodass = a.asscodass and car550.mes_vencimento = f.fpgultmes +1 and car550.movestor = '0' and car550.movvalpri < 0) , 0) as limite, (select sum(movvalpri) as consumo from car550 inner join car400 on car550.lojcodloj = car400.lojcodloj and car550.entcodent = car400.entcodent and car400.vintipvin = 1 where car550.entcodent = a.entcodent and car550.asscodass = a.asscodass and car550.mes_vencimento = f.fpgultmes +1 and car550.movestor = '0' and car550.movvalpri > 0) as consumo, (a.asslimcre - (select sum(movvalpri) as consumo from car550 inner join car400 on car550.lojcodloj = car400.lojcodloj and car550.entcodent = car400.entcodent and (car400.vintipvin = 1 or car400.lojcodloj = 9020) where car550.entcodent = a.entcodent and car550.asscodass = a.asscodass and car550.mes_vencimento = f.fpgultmes +1 and car550.movestor = '0')) as saldo, coalesce( (select max(movdatcom) from car550 where entcodent = a.entcodent and asscodass = a.asscodass), (select max(liqdatcom) from car10001 where entcodent = a.entcodent and liqcodass = a.asscodass) ) as data_ultima_autorizacao, f.fpgrazsoc as nomfpg, coalesce(t.cartao_numero, c.carnumcar) as numeroCartao, case a.asssitass when '0' then 'L' when '' then 'L' else 'B' END as status from car100 as a inner join fonte_pg as f on f.fpgcodfpg = a.asscodfpg and f.fpgcodent = a.entcodent left join car500 as c on c.entcodent = a.entcodent and c.asscodass = a.asscodass and a.assultvia = c.carviacar left join tbz_cartao as t on t.entidade_codigo = a.entcodent and t.associado_codigo = a.asscodass and t.cartao_via = a.assultvia and t.cartao_tipo = '0' where f.fpgcodent = a.entcodent and f.fpgcodfpg = a.asscodfpg

As you can see, in the columns called limit, consumption and balance, I assembled the result according to a clause (using another query).

However, when I search multiple records, the query becomes somewhat slow. Would someone have a reading tip for another approach where I can optimize what I need to do?

Answer 1

Try something like this:

   WITH querys AS (
                            SELECT COALESCE(sum(movvalpri), 0) AS limite, null AS consumo, null AS saldo, null AS data_ultima_autorizacao
                             FROM car550
                             INNER JOIN car400 ON car550.lojcodloj = car400.lojcodloj
                             AND car550.entcodent = car400.entcodent
                             AND (car400.vintipvin = 1
                                  OR car400.lojcodloj = 9020)
                             WHERE car550.entcodent = a.entcodent
                               AND car550.asscodass = a.asscodass
                               AND car550.mes_vencimento = f.fpgultmes +1
                               AND car550.movestor = '0'
                               AND car550.movvalpri < 0
                               UNION ALL

                               SELECT null AS limite, COALESCE(sum(movvalpri), 0) AS consumo, null AS saldo, null AS data_ultima_autorizacao
                                FROM car550
                                INNER JOIN car400 ON car550.lojcodloj = car400.lojcodloj
                                AND car550.entcodent = car400.entcodent
                                AND car400.vintipvin = 1
                                WHERE car550.entcodent = a.entcodent
                                AND car550.asscodass = a.asscodass
                                AND car550.mes_vencimento = f.fpgultmes +1
                                AND car550.movestor = '0'
                                AND car550.movvalpri > 0
                                UNION ALL

                                SELECT null AS limite, null AS consumo, COALESCE(sum(movvalpri, 0)) AS saldo, null AS data_ultima_autorizacao
       FROM car550
       INNER JOIN car400 ON car550.lojcodloj = car400.lojcodloj
       AND car550.entcodent = car400.entcodent
       AND (car400.vintipvin = 1
            OR car400.lojcodloj = 9020)
       WHERE car550.entcodent = a.entcodent
         AND car550.asscodass = a.asscodass
         AND car550.mes_vencimento = f.fpgultmes +1
         AND car550.movestor = '0'
 UNION ALL
 SELECT null AS limite, null AS consumo, null AS saldo, COALESCE((SELECT max(movdatcom)
               FROM car550
               WHERE entcodent = a.entcodent
                 AND asscodass = a.asscodass),
              (SELECT max(liqdatcom)
               FROM car10001
               WHERE entcodent = a.entcodent
                 AND liqcodass = a.asscodass)) AS data_ultima_autorizacao
                 )
    SELECT a.entcodent AS codent,
           a.asscodfpg AS codfpg,
           a.asscodass AS codigo,
           a.assmatass AS matricula,
           a.assnomass AS nome,
           a.asslogema AS email,
           CASE a.assnivass
               WHEN '0' THEN 'P'
               WHEN '1' THEN 'O'
           END AS nivel,
           f.fpgrazsoc AS fonte_pagadora,
           a.asslimcre - querys.limite AS limite,
           querys.consumo,
           (a.asslimcre - saldo) AS saldo,
           data_ultima_autorizacao,
           f.fpgrazsoc AS nomfpg,
           coalesce(t.cartao_numero, c.carnumcar) AS numeroCartao,
           CASE a.asssitass
               WHEN '0' THEN 'L'
               WHEN '' THEN 'L'
               ELSE 'B'
           END AS status
    FROM car100 AS a, querys
    INNER JOIN fonte_pg AS f ON (f.fpgcodfpg = a.asscodfpg AND f.fpgcodent = a.entcodent)
    LEFT JOIN car500 AS c ON (c.entcodent = a.entcodent AND c.asscodass = a.asscodass AND a.assultvia = c.carviacar)
    LEFT JOIN tbz_cartao AS t ON (t.entidade_codigo = a.entcodent AND t.associado_codigo = a.asscodass AND t.cartao_via = a.assultvia AND t.cartao_tipo = '0');

WITH provides a way to write sub-consultations for use in a larger SELECT query. The subsettes, which are often referred to as common table expressions or Ctes, can be thought of as defining temporary tables that exist only for this query. A use of this feature is to break complicated queries into simpler parts.

Also, I recommend the following reading[https://wiki.postgresql.org/wiki/Dicas_de_Performance_em_aplica%C3%A7%C3%B5es_com_PostgreSQL]