Query Performance - Postgresql

0

I have the following situation, I need to bring data from a vehicle table of the system in which I work and another with integrated data of a partner system, because integration is not very consistent data and the way to connect the tables can be by chassis , badge, or id:

select COUNT(*) from syo_veiculo = 290518
select COUNT(*) from syo_oficina = 945336

select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi

from syo_veiculo vei
 join syo_oficina ofi ON vei.id_veiculo = ofi.id_produto OR vei.ds_placa = ofi.ds_placa OR vei.no_chassi = ofi.no_chassi

where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000
group by vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, empresaofi

= 879 linhas em 30622 ms, 30337 ms, 30275 ms

Note that the result of the query was obtained in about 30 seconds, without grouping, would bring 1078 rows at the same time.

Alternative Query:

select *
from (
    select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi

    from syo_veiculo vei
     join syo_oficina ofi ON vei.id_veiculo = ofi.id_produto

    where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000

    union all

    select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi

    from syo_veiculo vei
     join syo_oficina ofi ON vei.ds_placa = ofi.ds_placa

    where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000

    union all

    select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi

    from syo_veiculo vei
     join syo_oficina ofi ON vei.no_chassi = ofi.no_chassi

    where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000
) as tbaux
group by id_veiculo, no_os, id_cliente, id_empresa, dt_emissao, empresaofi

= 879 linhas em 1969 ms, 791 ms, 724 ms

Note that the return came much faster, the same 879 rows without the collation would bring 2172 rows at the same time.

  • I want to understand why the query with union all where it has many more validations takes much less time, does not make sense to me, even more that the result was the same

  • Is there any exception in these cases union all X join com OR , for example an FK in the workshop table could make any difference in favor of join ?

I want to understand why I want to better plan my next queries and get the best performance.

    
asked by anonymous 29.10.2015 / 12:21

1 answer

0

Try the following:

    WITH tbaux  AS
    ( select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi
        from syo_veiculo vei
        join syo_oficina ofi ON vei.id_veiculo = ofi.id_produto
        where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000

        union all

        select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi

        from syo_veiculo vei
        join syo_oficina ofi ON vei.ds_placa = ofi.ds_placa
        where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000

        union all

        select vei.id_veiculo, vei.id_cliente, vei.id_empresa, ofi.no_os, ofi.dt_emissao, ofi.id_empresa as empresaofi
        from syo_veiculo vei
        join syo_oficina ofi ON vei.no_chassi = ofi.no_chassi

        where vei.dt_venda BETWEEN 1435100000000 AND 1435900000000)
SELECT * FROM tbaux group by id_veiculo, no_os, id_cliente, id_empresa, dt_emissao, empresaofi;

It's also interesting to check table indexes. You can also run some bank maintenance routines (Vacuum Analyze, REIDEX and CLUSTER).

But remember that it is recommended that the vacuum be done at a time when the bank is not being used.

    
17.11.2015 / 18:57