New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
FB3 Optimiser chooses less efficient plan than FB2.5 optimiser [CORE5965] #6219
Comments
Commented by: Haakon Nergaard (haakon) Code for creating and populating table to reproduce the issue |
Modified by: Haakon Nergaard (haakon)Attachment: optimizer_test_setup.sql [ 13311 ] |
Commented by: Haakon Nergaard (haakon) Moved code to create and populate table to a file attachment |
Modified by: Haakon Nergaard (haakon)description: The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Example code below: /** create generator gen_opt_test_id; create table opt_test ( alter table opt_test add constraint pk_opt_test primary key (id); set term ^ ; create or alter trigger opt_test_bi for opt_test set term ; ^ commit; set term ^ ; execute block
end ^ set term ; ^ /** create index opt_test_idx1 on opt_test (clid, cust_type, cust_id); set statistics index opt_test_idx1; /** => The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and polluting table in attachment. Example code below: /** |
Commented by: @dyemanov Please post query plans for both v2.5.8 and v3.0.4. |
Commented by: @livius2 I can confirm this on FB3 query: PLAN SORT (OPT_TEST INDEX (OPT_TEST_IDX1)) query: PLAN (OPT_TEST ORDER OPT_TEST_IDX3) |
Commented by: Haakon Nergaard (haakon) As the data is "random" the exact amount of fetches will vary slightly, but the difference should be pretty easy to spot. FB2.5.8: Query Plan FB3.0.4: Query Plan |
Modified by: Haakon Nergaard (haakon)description: The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and polluting table in attachment. Example code below: /** => The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and populating table in attachment. Example code below: /** |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 Beta 1 [ 10750 ] Fix Version: 3.0.5 [ 10885 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Haakon Nergaard (haakon)
Is related to CORE5795
Is related to CORE5070
Is related to CORE5481
Is related to CORE5845
Attachments:
optimizer_test_setup.sql
The optimiser in FB3 fails to use an index when there's an extra check in the where clause. The optimiser in FB2.5 can figure it out nicely. Code for creating and populating table in attachment. Example code below:
/**
* Here's the select. Please test with and without "sysid"
*/
select * from opt_test
where
--sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc
Commits: 077a2a3 3103f72
The text was updated successfully, but these errors were encountered: