Query plan with hash join

[Christian Kaufmann]

I have the following query

select
  E.BSENTITYID, SP.VALUEINTEGER as SEGMENTID, DAS.VALUEINTEGER as STARTSEGMENT, DES.VALUEINTEGER as ENDSEGMENT,
  DA.VALUEINTEGER as STARTDISTANZ, PTA.VALUESTRING as STARTPUNKT, DE.VALUEINTEGER as ENDDISTANZ, PTE.VALUESTRING as ENDPUNKT
  from BSENTITY E
  left join BSENTITYVALUE SP on E.BSENTITYID = SP.BSENTITYID and SP.VALUETYPE = 590
  left join BSENTITYVALUE DAS on SP.VALUEINTEGER = DAS.BSENTITYID and DAS.VALUETYPE = 508
  left join BSENTITYVALUE DES on SP.VALUEINTEGER = DES.BSENTITYID and DES.VALUETYPE = 501
  left join BSENTITYVALUE DA on E.BSENTITYID = DA.BSENTITYID and DA.VALUETYPE = 508
  left join BSENTITYVALUE PTA on E.BSENTITYID = PTA.BSENTITYID and PTA.VALUETYPE = 510
  left join BSENTITYVALUE DE on E.BSENTITYID = DE.BSENTITYID and DE.VALUETYPE = 501
  left join BSENTITYVALUE PTE on E.BSENTITYID = PTE.BSENTITYID and PTE.VALUETYPE = 502
where E.ENTITYTYPE = 1038

Using Firebird 3.0 this is much slower compared to PostgreSQL for example. It looks like Firebird does a lookup for each record in BSENTITY. Other SQL DB's build a hash table with the joined subsets.

Is there a way to force Firebird to use a better plan?

Christian

[Tomasz Dubiel]

"In Firebird 3.0, in case the of joining big and small tables, HASH JOIN could be much faster than normal join which uses «nested loop» with index. To make Firebird optimizer to use HASH join, use +0 in the join condition: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Check the result of the optimization before putting it to the production!"
Point 38 from:

https://ib-aid.com/br/articles/45-ways-to-speed-up-firebird-database/

[Christian Kaufmann]

I found this article and tried the trick. But the plan didn't change probably because the joined table is not really small.

Christian

[Mark Rotteveel]

What is the plan or explained plan of the query? Are the statistics of
your indexes up-to-date?

Mark
--
Mark Rotteveel

[Karol Bieniaszewski]

>> I found this article and tried the trick. But the plan didn't change probably because the joined table is not really small.

 

Then you have included +0 in the wrong place…

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/fb3d6f00-4286-4647-a811-b491275e87e7n%40googlegroups.com.

 

[Mark Rotteveel]

On 23-08-2022 16:59, Karol Bieniaszewski wrote:
> >> I found this article and tried the trick. But the plan didn't change
> probably because the joined table is not really small.
>
> Then you have included +0 in the wrong place…

Such tricks fool the optimizer by presenting it with an expression that
won't use an index, but that doesn't mean it will always result in a
hash join.

Mark
--
Mark Rotteveel

[Karol Bieniaszewski]

Yes Mark,

but if it do not resilt in hash join then it result in NATURAL scan

so the plan must changed then 😉

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d28da423-2776-e8e1-1826-0bb695beb6f1%40lawinegevaar.nl.

 

[Dmitry Yemanov]

23.08.2022 16:04, Christian Kaufmann wrote:
>
> Using Firebird 3.0 this is much slower compared to PostgreSQL for
> example. It looks like Firebird does a lookup for each record in
> BSENTITY. Other SQL DB's build a hash table with the joined subsets.
> Is there a way to force Firebird to use a better plan?

No, Firebird currently does not support hashing for outer joins.


Dmitry