[firebird-support] Informing the devs about optimizer issues / alternative plans

Hello. We have been trying to optimize one of our largest databases (firebird 3.0.4) , and in doing so, we managed to greatly improve the execution of one specific query by manually changing the PLAN. I am wondering if the firebird team is interested in receiving detailed feedback of such c

[firebird-support] Firebird optmizer cop-out

>From the docs about the optmizer, there is a small section : Cop-Out The user can pick or influence the optimizer’s decision. The Firebird optimizer actually uses all three Could anyone explain me further how the user can go about to effectively pick or influence the optimizers decision? By

Re: [firebird-support] Firebird optmizer cop-out

05.12.2018 12:37, Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] wrote: > Could anyone explain me further how the user can go about to effectively > pick or influence the optimizers decision? Join order can be forced by using outer join. Using of indexes can be disabled by cha

ODP: [firebird-support] substring similar - "Invalid SIMILAR TOpattern"

Thank you very much Mark for detailed info but i have more questions. I am really curious. Is this sql standard concept that i must do this in this crap way? SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' escape '#') FROM RDB$DATABASE Also strange that i must consume whol

Re: [firebird-support] Informing the devs about optimizer issues / alternative plans

Rudi Feijó wrote: > We have been trying to optimize one of our largest databases (firebird > 3.0.4) , and in doing so, we managed to greatly improve the execution of one > specific query by manually changing the PLAN. > > I am wondering if the firebird team is interested in receiving detailed

Re: [firebird-support] Informing the devs about optimizer issues / alternative plans

Hi, first you can share your query and query plan for it, and what you have changed and how your modified query plan looks like. Also check if you have problem with your query under newest snapshot as it contain recent optimizer fixes Regards,Karol Bieniaszewski null

Re: ODP: [firebird-support] substring similar - "Invalid SIMILAR TOpattern"

On 5-12-2018 18:00, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] wrote: > Is this sql standard concept that i must do this in this crap way? Yes, it is specified in SQL:2016, section 6.32 ", "". > SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' > esc

RE: [firebird-support] Firebird optmizer cop-out

>Join order can be forced by using outer join. >Using of indexes can be disabled by changing of simple field usage to an >expression. >Preferences between ORDER and SORT plans (more commonly between "first row" >and "all >rows" strategies) can be changed by using FIRST/ROWS. >Index statistics re

ODP: ODP: [firebird-support] substring similar - "Invalid SIMILARTOpattern"

Thank you once again. Now it is much clear that sql standard have more functions and much useful. I will wait for future improvements in Firebird. I stay longer with udfs for now. Regards, Karol Bieniaszewski

Re: [firebird-support] Firebird optmizer cop-out

05.12.2018 19:25, Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] wrote: > One last question, does the optimizer actively learns and auto-improves from > queries if we > set the PLAN manually? No. No AI inside. Optimizer is using database statistics, including index statistic