Skip to content

Extremely slow to do "Left Join" on the same table #8568

Open
@luapfr

Description

@luapfr

This command below takes 40 minutes

SELECT
CM.Codigo,
CM.MERCADORIA,
Sum(E.quant) As Saldo,
AVG(E2.preco_custo) As Preco
FROM CADASTRO_MERCADORIAS CM
left Join ESTOQUE E ON (E.Cod_Mercadoria = CM.codigo)
left Join ESTOQUE E2 ON ( E2.Cod_Mercadoria = CM.Codigo )
Group By 1, 2

The same database in PostgresSQL takes 16 seconds

The link below has the backup of the Database made in Firebird 3
and the SQL commands to create and insert the data in SQL

https://mega.nz/file/k6gUhBpJ#4gXE7oLSW_DJdKB9UZaMUQvsI9GOPiYYJ8wqciHUVdw

Note:
I can't use Inner Join

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions