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
Ineffective join order is used for a cross join with non-indexed predicates [CORE3127] #3504
Comments
Commented by: Ray Holme (rholme) All files were sent to Paul Beach a month or so ago, but I am going to attach them again here. the enclosed query runs against the enclosed 2.0.5 |
Commented by: Ray Holme (rholme) these are the workaround script (addindex) the output of the sql runs (timinngs incl.) and the script which runs the query the DB and UDF will be attached in the next attachment as I am limited to 2 |
Modified by: Ray Holme (rholme)Attachment: addindex.ddl [ 11764 ] Attachment: out.txt [ 11765 ] Attachment: test_xx [ 11766 ] |
Commented by: Ray Holme (rholme) DB and Linux 64 bit UDF |
Modified by: Ray Holme (rholme)Attachment: vetAdmin.gbak.gz [ 11767 ] Attachment: http://vetAdmin.so [ 11768 ] |
Commented by: Sean Leyne (seanleyne) What happens if you try: SELECT |
Commented by: Sean Leyne (seanleyne) Please post the PLAN used by the different SELECT statements. |
Commented by: Sean Leyne (seanleyne) The original SQL seems to be missing the relationship between patients and people. How are the tables related? |
Commented by: @dyemanov The attached backup fails to restore with v2.0.5: unsuccessful metadata update. |
Commented by: Ray Holme (rholme) 1) Sean and using EXISTS (net chage from 31.5 seconds to 30.5 seconds) where 2.0.5 does it in .15 seconds 2) forgot to post plans - will upload - thanks Sean 3) this (see DB name) is a vetinarian system 4) Dimitry - I restored this OLD copy of the DB using GNAK Version: LI-V2.0.5.13206 Firebird 2.0 RESULTS of Sean's attempt and plans being posted shortly |
Commented by: Ray Holme (rholme) Here are the timings - 2.0.5, 2.1.3 all variants including the Sean EXISTS request |
Modified by: Ray Holme (rholme)Attachment: timings [ 11770 ] |
Commented by: @dyemanov What is the plan on the original db (without index fix_bug1) and v2.0.5? |
Commented by: Sean Leyne (seanleyne) 1 - I did not try the restore. 2 - What fields make up the join between patient and owner? 3 - what other indexes are defined for those tables? |
Commented by: Ray Holme (rholme) All timings and plans can be found in the uploaded file - timings. About to upload a PC dll so those using can have the UDF. the query shows the join fields and the indices are in the DB but repeated here. FIX_BUG1 UNIQUE INDEX ON PATIENTS(SOUNDEX_NAME, OFFICE_ID, DEACT_CODE, BILLTO_ID, PATIENT_ID) INDX_PERS1 UNIQUE INDEX ON PEOPLE(PERSON_ID) |
Commented by: Ray Holme (rholme) OK Dimitri - this should do it - there is the DLL |
Modified by: Ray Holme (rholme)Attachment: vetAdmin.dll [ 11771 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovVersion: 2.5 RC3 [ 10381 ] Version: 2.0.6 [ 10303 ] Version: 2.5 RC2 [ 10372 ] Version: 2.5 RC1 [ 10362 ] Version: 3.0 Initial [ 10301 ] Version: 2.1.3 [ 10302 ] Version: 2.5 Beta 2 [ 10300 ] Version: 2.5 Beta 1 [ 10251 ] Version: 2.1.2 [ 10270 ] summary: somewhere after 2.0.5 - apparently 2.0.6 a significant change was made to the optimizer - I had a query that ran in 2 seconds take 40 => Ineffective join order is used for a cross join with non-indexed predicates |
Modified by: @dyemanovVersion: 2.5.0 [ 10221 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Submitted by: Ray Holme (rholme)
Is related to CORE2200
Attachments:
addindex.ddl
out.txt
test_xx
vetAdmin.gbak.gz
vetAdmin.so
timings
vetAdmin.dll
I have a DB, copy of the Linux UDF (can be eliminated as computed fields not referenced in query), copy of the query, timings under 2 releases of Linux (first seen on PC, then on MAC, but confirmed here on Linux), and an add Index statement that works around the problem for us.
The text was updated successfully, but these errors were encountered: