Issue Details (XML | Word | Printable)

Key: CORE-3127
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Ray Holme
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Ineffective join order is used for a cross join with non-indexed predicates

Created: 07/Sep/10 09:47 PM   Updated: 17/Mar/13 02:48 PM
Component/s: Engine
Affects Version/s: 2.1.2, 2.5 Beta 1, 2.5 Beta 2, 2.1.3, 3.0 Initial, 2.5 RC1, 2.5 RC2, 2.0.6, 2.5 RC3, 2.5.0
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. File addindex.ddl (0.1 kB)
2. Text File out.txt (1 kB)
3. File test_xx (0.5 kB)
4. File timings (0.4 kB)
5. File vetAdmin.dll (69 kB)
6. GZip Archive vetAdmin.gbak.gz (944 kB)
7. File vetAdmin.so (14 kB)

Environment: Reproducible with isql - tested in 2.0.6 and 2.1.3 - on MAC, Linux and PC platforms
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Ray Holme added a comment - 07/Sep/10 09:49 PM
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

Ray Holme added a comment - 07/Sep/10 09:53 PM
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

Ray Holme added a comment - 07/Sep/10 09:55 PM
DB and Linux 64 bit UDF

Sean Leyne added a comment - 07/Sep/10 10:15 PM
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)
  )
;

Sean Leyne added a comment - 07/Sep/10 10:15 PM
Please post the PLAN used by the different SELECT statements.

Sean Leyne added a comment - 07/Sep/10 10:18 PM
The original SQL seems to be missing the relationship between patients and people. How are the tables related?

Dmitry Yemanov added a comment - 08/Sep/10 03:27 AM
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.

Ray Holme added a comment - 08/Sep/10 12:03 PM
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

Ray Holme added a comment - 08/Sep/10 12:04 PM
Here are the timings - 2.0.5, 2.1.3 all variants including the Sean EXISTS request

Dmitry Yemanov added a comment - 08/Sep/10 04:55 PM
What is the plan on the original db (without index fix_bug1) and v2.0.5?

Sean Leyne added a comment - 08/Sep/10 05:48 PM
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?

Ray Holme added a comment - 08/Sep/10 06:37 PM
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)

Ray Holme added a comment - 08/Sep/10 06:38 PM
OK Dimitri - this should do it - there is the DLL