FirebirdSQL
Extremely slow to do "Left Join" on the same table
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
Create indicies: CREATE INDEX CADASTRO_MERCADORIAS_I1 ON CADASTRO_MERCADORIAS (CODIGO, MERCADORIA); CREATE INDEX ESTOQUE_I1 ON ESTOQUE (COD_MERCADORIA);
If you do not need any data from E and E2 then use exists: SELECT CM.Codigo, CM.MERCADORIA FROM CADASTRO_MERCADORIAS CM where exists (select 1 from ESTOQUE E where (E.Cod_Mercadoria = CM.codigo)) and exists (select 1 from ESTOQUE E2 where ( E2.Cod_Mercadoria = CM.Codigo)) Group By 1, 2
Runtime for this query: select count(1) from ( SELECT CM.Codigo, CM.MERCADORIA FROM CADASTRO_MERCADORIAS CM where exists (select 1 from ESTOQUE E where (E.Cod_Mercadoria = CM.codigo)) and exists (select 1 from ESTOQUE E2 where ( E2.Cod_Mercadoria = CM.Codigo)) Group By 1, 2 )
Plan
PLAN (E INDEX (ESTOQUE_I1)) PLAN (E2 INDEX (ESTOQUE_I1)) PLAN (CM ORDER CADASTRO_MERCADORIAS_I1) ------ Performance info ------ Prepare time = 0ms Execute time = 125ms Avg fetch time = 125,00 ms Current memory = 36 446 400 Max memory = 36 631 216 Memory buffers = 2 048 Reads from disk to cache = 701 Writes from cache to disk = 1 Fetches from cache = 140 346
The problem with your approach is that I need to use "Left Join" fields and with your approach I can't How would I execute this code with "Exists"
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
Why is it so fast in Postgres and not in Firebird ?
You tell us how PostgreSQL executes your query. What is query plan and data access method used?
I changed the SQL of the initial Post because I need access to "Left Join" fields and using "Exists" I don't have that type of access
I changed the SQL of the initial Post because I need access to "Left Join" fields and using "Exists" I don't have that type of access
I really don't know what Postgre does...
Ig you do sum and average on same table you can join it only once.
select count(1) from ( SELECT CM.Codigo, CM.MERCADORIA, Sum(E.quant) As Saldo, AVG(E.preco_custo) As Preco FROM CADASTRO_MERCADORIAS CM left Join ESTOQUE E ON (E.Cod_Mercadoria = CM.codigo) Group By 1, 2 )
COUNT 16846
Plan
PLAN JOIN (CM ORDER CADASTRO_MERCADORIAS_I1, E INDEX (ESTOQUE_I1)) ------ Performance info ------ Prepare time = 0ms Execute time = 797ms Avg fetch time = 797,00 ms Current memory = 36 435 536 Max memory = 36 574 480 Memory buffers = 2 048 Reads from disk to cache = 35 288 Writes from cache to disk = 1 Fetches from cache = 577 931
I can't join the same table, there have to be two "Left Joins". This example I posted is very simple, the complete SQL is much more complex and I actually need to access the same table several times. In some cases, I need 5 "LEFT JOINs" in the same table.
Exemple:
WITH Soma_Estoque_1 As ( Select E.Cod_Mercadoria, Sum(E.Quant) Saldo_Date From Estoque E Where (E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') And ( E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '' ) And E.data_emissao >= '01.12.2024' Group By E.Cod_Mercadoria )
SELECT CAD.Codigo, CAD.MERCADORIA, ST1.Saldo_Date, Sum(REL.Quant) Saldo_Total
FROM CADASTRO_MERCADORIAS CAD
left Join ESTOQUE REL ON (REL.Cod_Mercadoria = CAD.codigo) Left Join Soma_Estoque_1 ST1 on (ST1.Cod_Mercadoria = REL.Cod_Mercadoria )
Group By 1, 2, 3
with the creation of appropriate indexes, as suggested by András, the result is instantaneous
Norbert
De : luapfr @.> Envoyé : jeudi 15 mai 2025 16:45 À : FirebirdSQL/firebird @.> Cc : Subscribed @.***> Objet : Re: [FirebirdSQL/firebird] Extremely slow to do "Left Join" on the same table (Issue #8568)
https://avatars.githubusercontent.com/u/50739062?s=20&v=4 luapfr left a comment (FirebirdSQL/firebird#8568) https://github.com/FirebirdSQL/firebird/issues/8568#issuecomment-2883870624
I can't join the same table, there have to be two "Left Joins". This example I posted is very simple, the complete SQL is much more complex and I actually need to access the same table several times. In some cases, I need 5 "LEFT JOINs" in the same table.
Exemple:
WITH Soma_Estoque_1 As ( Select E.Cod_Mercadoria, Sum(E.Quant) Saldo_Tipo From Estoque E Where (E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') And ( E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '' ) And E.data_emissao >= '01.12.2024' Group By E.Cod_Mercadoria )
SELECT CAD.Codigo, CAD.MERCADORIA, ST1.Saldo_Date, Sum(REL.Quant) Saldo_Total
FROM CADASTRO_MERCADORIAS CAD
left Join ESTOQUE REL ON (REL.Cod_Mercadoria = CAD.codigo) Left Join Soma_Estoque_1 ST1 on (ST1.Cod_Mercadoria = REL.Cod_Mercadoria )
Group By 1, 2, 3
— Reply to this email directly, view it on GitHub https://github.com/FirebirdSQL/firebird/issues/8568#issuecomment-2883870624 , or unsubscribe https://github.com/notifications/unsubscribe-auth/BJEAUKTKFLWLMBPQEETOIC326SK4BAVCNFSM6AAAAAB5GBN7WWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDQOBTHA3TANRSGQ . You are receiving this because you are subscribed to this thread. https://github.com/notifications/beacon/BJEAUKUFZHBS2TS5H67FF2L26SK4BA5CNFSM6AAAAAB5GBN7WWWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTVL4RP2A.gif Message ID: @.***>
How strange, I created the indexes and here it takes more than 20 minutes
CREATE INDEX CADASTRO_MERCADORIAS_I1 ON CADASTRO_MERCADORIAS (CODIGO, MERCADORIA); CREATE INDEX ESTOQUE_I1 ON ESTOQUE (COD_MERCADORIA);
Are you sure it is instant ? Can you make a backup of the database and post it so I can test it ?
I tested with Firebird 3 and also with 5 and I see that 5 has no difference whatsoever over 3.
These are your data transmitted via mega.
Creating the two indexes shouldn't have taken more than a few seconds.
The server used is an old xeon with a mechanical disk and Rocky8 with FB5 WI-V6.3.0.1227.
Norbert
These are your data transmitted via mega. Creating the two indexes shouldn't have taken more than a few seconds. The server used is an old xeon with a mechanical disk and Rocky8 with FB5 WI-V6.3.0.1227. Norbert
Yes, index creation is not an issue The problem is the SQL execution that takes 20 minutes
If you can get the database and test it, you will see that this SQL is extremely slow and I don't know how to make it fast. I created the indexes suggested "omachtandras" above but they didn't solve anything.
WITH Soma_Estoque_1 As ( Select E.Cod_Mercadoria, Sum(E.Quant) Saldo_Date From Estoque E Where (E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') And ( E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '' ) And E.data_emissao >= '01.12.2024' Group By E.Cod_Mercadoria )
SELECT CAD.Codigo, CAD.MERCADORIA, ST1.Saldo_Date, Sum(REL.Quant) Saldo_Total
FROM CADASTRO_MERCADORIAS CAD
left Join ESTOQUE REL ON (REL.Cod_Mercadoria = CAD.codigo) Left Join Soma_Estoque_1 ST1 on (ST1.Cod_Mercadoria = REL.Cod_Mercadoria )
Group By 1, 2, 3
The problem is the SQL execution that takes 20 minutes
Without these indexes (or at leas one index on Cod_Mercadoria) it is not Firebird problem but a bad design of your database that lack keys and required indexes. Learn this: https://firebirdsql.org/en/community-news/new-article-data-access-methods-used-in-firebird
The problem is the SQL execution that takes 20 minutes
Without these indexes (or at leas one index on Cod_Mercadoria) it is not Firebird problem but a bad design of your database that lack keys and required indexes. Learn this: https://firebirdsql.org/en/community-news/new-article-data-access-methods-used-in-firebird
I'm sorry for using translate, I think you didn't understand me. I created the indexes and it didn't solve anything
My database has all the possible indexes, I know very well the need for indexes and FK keys, but I posted a demo database. If you could download the database and show me which indexes are missing to make this quick, I would be immensely grateful.
Probably the statistics on your existing indexes are out of date. For people who restored your backup, they are up to date. You may want to consider restoring the database yourself or execute set statistics on each existing index (including the indexes backing your primary keys!).
To make your query fast you must learn documentation above and check query plan. Then if you still think that this plan is wrong - post it with relevant DDL.
Probably the statistics on your existing indexes are out of date. For people who restored your backup, they are up to date. You may want to consider restoring the database yourself or execute
set statisticson each existing index (including the indexes backing your primary keys!).
I just made a backup with the indexes you suggested and I restored it and it's still slow Tell me, is this SQL running fast ?
See, I'm running it with IBExpert and I'm clicking ExecuteAll to bring all the records
Are you trying this SQL ?
WITH Soma_Estoque_1 As ( Select E.Cod_Mercadoria, Sum(E.Quant) Saldo_Date From Estoque E Where (E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') And ( E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '' ) And E.data_emissao >= '01.12.2024' Group By E.Cod_Mercadoria )
SELECT CAD.Codigo, CAD.MERCADORIA, ST1.Saldo_Date, Sum(REL.Quant) Saldo_Total
FROM CADASTRO_MERCADORIAS CAD
left Join ESTOQUE REL ON (REL.Cod_Mercadoria = CAD.codigo) Left Join Soma_Estoque_1 ST1 on (ST1.Cod_Mercadoria = REL.Cod_Mercadoria )
Group By 1, 2, 3
For me that query runs slow (Elapsed time = 316.339 sec). That is after I apply the indexes suggested by Andras for your other query (creation was near instantaneous, BTW), and calling set statistics for the previously existing indexes (I recreated the db from scratch using your scripts).
Adding an index on estoque(data_emissao) gives some improvement (Elapsed time = 103.571 sec):
create index estoque_data_emissao on estoque (data_emissao);
There are some additional things in your query I would address:
- Replace condition
(E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '')withE.Lixo is distinct from 'S' - Replace condition
( E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '' )withE.Arquivo is distinct from 'S' - Replace condition
E.data_emissao >= '01.12.2024'withE.data_emissao >= timestamp '2024-12-01'(if only for correctness of datatypes involved)
Doing so gives a further (minor) improvement: Elapsed time = 96.714 sec
I tried creating a partial index on estoque (data_emissao) (this is a Firebird 5 feature)
create index estoque_i2 on estoque (data_emissao) where lixo is distinct from 'S' and arquivo is distinct from 'S';
The improvement was negligible for my modified query (Elapsed time = 94.879 sec), probably because most of lixo and arquivo are NULL anyway, and had no change for yours.
I'm not sure what the problem could be, but I agree with you that it feels oddly slow.
Rewriting your query to the following (which has the same result), completes in 0.890 seconds:
select
CAD.Codigo,
CAD.MERCADORIA,
Sum(REL.Quant) filter (where REL.Lixo is distinct from 'S' and REL.Arquivo is distinct from 'S' and REL.data_emissao >= timestamp '2024-12-01') Saldo_Date,
Sum(REL.Quant) Saldo_Total
from CADASTRO_MERCADORIAS CAD
left Join ESTOQUE REL
on REL.Cod_Mercadoria = CAD.codigo
group by 1, 2
Legacy plan:
PLAN JOIN (CAD ORDER CADASTRO_MERCADORIAS_I1, REL INDEX (ESTOQUE_IDX1))
Explained plan:
Select Expression
-> Aggregate
-> Nested Loop Join (outer)
-> Table "CADASTRO_MERCADORIAS" as "CAD" Access By ID
-> Index "CADASTRO_MERCADORIAS_I1" Full Scan
-> Filter
-> Table "ESTOQUE" as "REL" Access By ID
-> Bitmap
-> Index "ESTOQUE_IDX1" Range Scan (full match)
(It uses Firebird 4 features, but you can replace the filter (where ...) with a case when ... within the sum)
As an aside, the original query in the TS runs in Elapsed time = 270.482 sec for me (with Andras' suggested indexes).
Are you trying this SQL ?
If you rewrite it to minimalize the record reading it could be fast (with previously suggested indicies):
with cad_dist as (select cd.Codigo, cd.MERCADORIA from CADASTRO_MERCADORIAS CD group by cd.Codigo, cd.MERCADORIA),
Soma_Estoque As (Select E.Cod_Mercadoria, Sum(E.Quant) Saldo_Total, Sum(iif((E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') and (E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '') and (E.data_emissao >= '01.12.2024'), E.Quant, 0)) Saldo_Date From Estoque E Group By E.Cod_Mercadoria )
SELECT CAD.Codigo, CAD.MERCADORIA, ST1.Saldo_Date, min(ST1.Saldo_Total) Saldo_Total FROM cad_dist CAD Left Join Soma_Estoque ST1 on (ST1.Cod_Mercadoria = CAD.Codigo )
Group By 1, 2, 3
Plan
PLAN SORT (JOIN (CAD CD ORDER CADASTRO_MERCADORIAS_I1, ST1 E ORDER ESTOQUE_I1)) ------ Performance info ------ Prepare time = 0ms Execute time = 1s 906ms Avg fetch time = 38,90 ms Current memory = 58 676 640 Max memory = 106 340 960 Memory buffers = 2 048 Reads from disk to cache = 35 566 Writes from cache to disk = 1 Fetches from cache = 858 338
Enchanced Info: +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ | Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges | | | Total | reads | reads | | | | | | | +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ |CADASTRO_MERCADORIAS | 0 | 16846 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |ESTOQUE | 0 | 276144 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
@omachtandras That query doesn't produce the exact same result, e.g. some values of SALDO_DATE will be 0.0 instead of NULL.
@omachtandras That query doesn't produce the exact same result, e.g. some values of
SALDO_DATEwill be 0.0 instead of NULL.
Mark, you are right, maybe this one is better:
with cad_dist as (select cd.Codigo, cd.MERCADORIA from CADASTRO_MERCADORIAS CD group by cd.Codigo, cd.MERCADORIA),
Soma_Estoque As (Select E.Cod_Mercadoria, Sum(E.Quant) Saldo_Total, Sum(iif((E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') and (E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '') and (E.data_emissao >= '01.12.2024'), E.Quant, 0)) Saldo_Date, Max(iif((E.Lixo <> 'S' Or E.Lixo is Null Or E.Lixo = '') and (E.Arquivo <> 'S' Or E.Arquivo is Null Or E.Arquivo = '') and (E.data_emissao >= '01.12.2024'), 1, 0)) Saldo_count From Estoque E Group By E.Cod_Mercadoria )
SELECT CAD.Codigo, CAD.MERCADORIA, iif(st1.saldo_count = 0, null, ST1.Saldo_Date) saldo_date, min(ST1.Saldo_Total) Saldo_Total FROM cad_dist CAD Left Join Soma_Estoque ST1 on (ST1.Cod_Mercadoria = CAD.Codigo )
Group By 1, 2, 3
Show query plan and execution statistics
I know you asked the OP, but for me (with Andras' suggested indexes, and the indexes created as described in my previous comment), the plans for that query are, on Firebird 5.0.2 (using ISQL with an embedded connection):
Legacy:
PLAN SORT (JOIN (JOIN (CAD NATURAL, REL INDEX (ESTOQUE_IDX1)), ST1 E ORDER ESTOQUE_IDX1 INDEX (ESTOQUE_DATA_EMISSAO)))
Explained:
Select Expression
-> Aggregate
-> Sort (record length: 830, key length: 664)
-> Nested Loop Join (outer)
-> Nested Loop Join (outer)
-> Table "CADASTRO_MERCADORIAS" as "CAD" Full Scan
-> Filter
-> Table "ESTOQUE" as "REL" Access By ID
-> Bitmap
-> Index "ESTOQUE_IDX1" Range Scan (full match)
-> Filter
-> Aggregate
-> Filter
-> Table "ESTOQUE" as "ST1 E" Access By ID
-> Index "ESTOQUE_IDX1" Range Scan (full match)
-> Bitmap
-> Index "ESTOQUE_DATA_EMISSAO" Range Scan (lower bound: 1/1)
Statistics:
Current memory = 139136912
Delta memory = 238768
Max memory = 253436640
Elapsed time = 103.101 sec
Buffers = 8192
Reads = 3020
Writes = 0
Fetches = 51711549
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
RDB$DATABASE | 4| | | | | | | |
RDB$FIELDS | | 125| | | | | | |
RDB$INDICES | | 25| | | | | | |
RDB$RELATION_FIELDS | | 250| | | | | | |
RDB$RELATIONS | | 10| | | | | | |
RDB$FORMATS | | 2| | | | | | |
RDB$SECURITY_CLASSES | | 4| | | | | | |
RDB$TYPES | | 1| | | | | | |
RDB$TRIGGERS | | 3| | | | | | |
RDB$CHARACTER_SETS | | 2| | | | | | |
RDB$COLLATIONS | | 1| | | | | | |
CADASTRO_MERCADORIAS | 16846| | | | | | | |
ESTOQUE | | 21101937| | | | | | |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
For comparison, my equivalent query results in the following statistics:
Current memory = 139124944
Delta memory = 226800
Max memory = 139230784
Elapsed time = 0.911 sec
Buffers = 8192
Reads = 3035
Writes = 0
Fetches = 580212
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
RDB$DATABASE | 4| | | | | | | |
RDB$FIELDS | | 125| | | | | | |
RDB$INDICES | | 18| | | | | | |
RDB$RELATION_FIELDS | | 250| | | | | | |
RDB$RELATIONS | | 10| | | | | | |
RDB$FORMATS | | 2| | | | | | |
RDB$SECURITY_CLASSES | | 4| | | | | | |
RDB$TYPES | | 1| | | | | | |
RDB$TRIGGERS | | 3| | | | | | |
RDB$CHARACTER_SETS | | 2| | | | | | |
RDB$COLLATIONS | | 1| | | | | | |
CADASTRO_MERCADORIAS | | 16846| | | | | | |
ESTOQUE | | 276144| | | | | | |
--------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
As far as I can see, these codes solve the problem, but I realize that the big problem is making two "Left Join" accesses to the same table The way my friends solved the problem was by putting everything in just one table in the CTE.
Isn't this a Firebird problem when working with two "Left Join" accesses to the same table?
I think it's a problem with firebird's query optimizer because the same table with the same number of records in postgresql is much faster.
Test it with postgresql using the SQL inserts in my link.
The firebird developers should look into this, how do I post this as a firebird problem for a possible future solution?
@luapfr You already posted it in issues, which is the right place for bug reports or feature requests.
For future reference, if this had been a support question, the better place would have been firebird-support or the Discussions section here on GitHub.
I don't know if this can help developers, but here is the Postgres log
You can provide any information about Postgres, as my system can execute the same Firebird queries in Postgres. I currently have clients using both databases in production.
Merge outer join... Yes, that's what Firebird so far cannot do.
Could this be a factor in Postgres speed ?
If so, I think it would be great if Firebird developers worked better on optimizing queries and left other things alone, since performance is essential.
This is one of the cases I posted, but in my database with more than 280 tables and several types of reports, there is not a single report that Firebird beats Postgres in.
There are other cases where the difference is even greater in favor of Postgres.
