Bad performance on simple two joins query on tables with composed index - minutes on Firebird 5 compared to Firebird 3 miliseconds

[Storkware86]

Hello, in our real-world application we've tested Firebird 5 performance, and it appears to be significantly better in general.

But we have found some issue!

I've included a database file TestDB5.zip, which contains three tables: TABLE 1 has primary key composed of two fields TABLE 2 adds one more string field to this key; so it has 3 fields key TABLE 3 adds one more string field to this key; so it has 4 fields key

The following query, which joins all three tables, took several minutes to execute:

SELECT t2.*, t3.CODE15, t3.PRICE, t1.NAME FROM TABLE2 t2 JOIN TABLE3 t3 ON t3.ID1 = t2.ID1 AND t3.ID2 = t2.ID2 AND t3.CODE30 = t2.CODE30 JOIN TABLE1 t1 ON t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2

• If i change last join to LEFT JOIN, then it runs about 1 sec.
• If TABLE1 had a primary key consisting of only one field, the query would execute in approximately 1 second.
• The query also runs in about 1 second on Firebird 3.

I've also included the Firebird 3 database for comparison purposes.

Regards, Jaroslav

[sim1984]

How long will the query take to execute

SELECT COUNT(*)
FROM TABLE2 t2
JOIN TABLE3 t3 ON t3.ID1 = t2.ID1 AND t3.ID2 = t2.ID2 AND t3.CODE30 = t2.CODE30
JOIN TABLE1 t1 ON t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2

You haven't provided plans for this query.

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (inner)
                -> Hash Join (inner)
                    -> Table "TABLE3" as "T3" Full Scan
                    -> Record Buffer (record length: 33)
                        -> Table "TABLE1" as "T1" Full Scan
                -> Record Buffer (record length: 153)
                    -> Table "TABLE2" as "T2" Full Scan

I suspect that when retrieving all the records. There won't be much difference between Firebird 3.0 and 5.0.

[Storkware86]

@sim1984 1) select count(*) takes dozens of minutes, it has no effect at all 2) I am sorry, but I have provided the database file and the SQL. It's all you need to replicate the issue.

Please do not suspect anything, you can try it. There is a huge difference between Firebird 3.0 and 5.0 when retrieving all the records.

I can also provide Firebird 3 database, which I am doing TestDB3.zip

[Storkware86]

Hello, is there anything new according to this issue? For us it's a stop-case for using Firebird 5. There's a poor performance on a simple query that can happen in any database.

[dyemanov]

The problem is that when T2 is joined to the other two tables, the join condition becomes:

t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2 AND t3.ID1 = t2.ID1 AND t3.ID2 = t2.ID2 AND t3.CODE30 = t2.CODE30

while the hash join could utilize up to 4 keys, so the last condition was ignored during hashing/probing, thus exploding the result set to be filtered later (which takes quite a long time for almost 3 billion rows ;-).

Fixed now, thanks for reporting.

[pavel-zotov]

Tables in explained plans are specified in the same order for snapshots before and after fix. Difference can be seen only in added "keys: " clause. This clause currently exists only in FB 6.x, commit: c50b0aa6 Because of this, test min_version currently set to 6.0.

[Storkware86]

Thank you Dmitry!.. Just to be sure that we've dug deep enough - does it make sense, based on your understanding of the issue, that if we changed on of the joins to a LEFT JOIN, the performance would be much better? And that there was no problem in Firebird 3.

I mean like that:

SELECT COUNT(*) FROM TABLE2 t2 JOIN TABLE3 t3 ON t3.ID1 = t2.ID1 AND t3.ID2 = t2.ID2 AND t3.CODE30 = t2.CODE30 LEFT JOIN TABLE1 t1 ON t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2

[dyemanov]

FB 3 used hash joins less often (it just didn't contain the code that failed), so this is expected. LEFT JOINs are optimized completely differently and hash joining does not work for them (yet), so again this is expected.

[Storkware86]

Thanks again.. according to our first test results it looks good, We will keep on testing and I'll let you know if we find something.