Skip to content
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

Open
firebird-automations opened this issue Sep 8, 2010 · 23 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

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
proprietary and confidential DB (UDFs for linux enclosed and guaranteed
safe), also enclosed test output - if I missed anything let me know
under 2.0.5 Linux it takes about 1/8 second
under 2.1.3 MAC - 64, PC and Linux-64 it takes 40 + seconds
under 2.06 32 bit PC it takes also 40 seconds

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Ray Holme (rholme)

Attachment: addindex.ddl [ 11764 ]

Attachment: out.txt [ 11765 ]

Attachment: test_xx [ 11766 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

DB and Linux 64 bit UDF

@firebird-automations
Copy link
Collaborator Author

Modified by: Ray Holme (rholme)

Attachment: vetAdmin.gbak.gz [ 11767 ]

Attachment: http://vetAdmin.so [ 11768 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

What happens if you try:

SELECT
p.first_name, p.last_name, pt.patient_id, pt.billto_id, pt.species_id, pt.color, pt.owner_label, pt.deact_code
FROM patients pt, people p
WHERE
pt.office_id = 1
and pt.deact_code is null
and pt.soundex_name like 'M24%'
and (
pt.billto_id = p.person_id
or EXISTS (select 1 from patient_owners po where po.patient_id = pt.patient_id and po.person_id = pt.billto_id)
)
;

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Please post the PLAN used by the different SELECT statements.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

The original SQL seems to be missing the relationship between patients and people. How are the tables related?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The attached backup fails to restore with v2.0.5:

unsuccessful metadata update.
TABLE INVOICE_LINES.
Can't have relation with only computed fields or constraints.

@firebird-automations
Copy link
Collaborator Author

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
adding my index (added in DB uploaded, sorry) makes 2.1.3 do it in .48 secs (tolerable) and 2.0.5 do it in .20 secs
(why slower - beats me, but then again perhaps the numbers at sub-second level mean nothing)

2) forgot to post plans - will upload - thanks Sean

3) this (see DB name) is a vetinarian system
each animal has one primary owner (see table:Patients -> People)
animals may also have multiple secondary owners (table: Patient_owners)
- hence the query you saw

4) Dimitry - I restored this OLD copy of the DB using GNAK Version: LI-V2.0.5.13206 Firebird 2.0
- Sean apparenly did too (maybe not as he asked me to try the query)

RESULTS of Sean's attempt and plans being posted shortly

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

Here are the timings - 2.0.5, 2.1.3 all variants including the Sean EXISTS request

@firebird-automations
Copy link
Collaborator Author

Modified by: Ray Holme (rholme)

Attachment: timings [ 11770 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What is the plan on the original db (without index fix_bug1) and v2.0.5?

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

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_PATN1 UNIQUE INDEX ON PATIENTS(PATIENT_ID)
INDX_PATN2 UNIQUE INDEX ON PATIENTS(OWNER_LABEL, BILLTO_ID, PATIENT_ID)
INDX_PAON1 UNIQUE INDEX ON PATIENT_OWNERS(PERSON_ID, PATIENT_ID)
INDX_PAON2 UNIQUE INDEX ON PATIENT_OWNERS(PATIENT_ID, PERSON_ID)

INDX_PERS1 UNIQUE INDEX ON PEOPLE(PERSON_ID)
INDX_PERS2 UNIQUE INDEX ON PEOPLE(LAST_NAME, FIRST_NAME, PERSON_ID)
INDX_PERS3 UNIQUE INDEX ON PEOPLE(FIRST_NAME, PERSON_ID)

@firebird-automations
Copy link
Collaborator Author

Commented by: Ray Holme (rholme)

OK Dimitri - this should do it - there is the DLL

@firebird-automations
Copy link
Collaborator Author

Modified by: Ray Holme (rholme)

Attachment: vetAdmin.dll [ 11771 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE2200 [ CORE2200 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.0 [ 10221 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment