Issue Details (XML | Word | Printable)

Key: CORE-1086
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: ohenri
Votes: 3
Watchers: 4
Operations

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

Select from ... where in (select ...) got unoptimized Execution plan

Created: 11/Jan/07 06:00 PM   Updated: 11/Oct/14 09:01 AM
Component/s: Engine
Affects Version/s: 2.0.0
Fix Version/s: None

Environment:
Firebird 2.0 and also Firebird 1.5.3
Windows Server 2003 SP1
Issue Links:
Duplicate
 


 Description  « Hide
On the statement

select * from XI_ITEM XI
where XDHID in (select XDHID from XH_HDR XH where XH.XDST = '6');

the execution plan is not as expected.

Expected:
- one time exec of the "in-select" on XH_HDR
- then read all rows of the outer select XI_ITEM
  and check to the result set of XH_HDR

In fact,
- it reads all rows of the XI_ITEM
- executes for every row of XI_ITEM the "in-select" on XH_HDR

Since the number of rows XDH : XDI is 1 : 100, it results in a long time response.

Compared to other DBMS like MS-SQL or Gupta's SQL Base, they work as expected, means response time less than one second.




Introduction for reproducing the problem

Two tables in a classic situation, a header and an Item table.


CREATE TABLE XH_HDR
(
  XDHID INTEGER NOT NULL,
  XDST CHAR( 1) NOT NULL,
 PRIMARY KEY (XDHID)
);

CREATE TABLE XI_ITEM
(
  XDHID INTEGER NOT NULL,
  XITEMNO INTEGER NOT NULL,
  XDISZE INTEGER,
 PRIMARY KEY (XDHID, XITEMNO)
);

RI is not necessary but for better documentation

ALTER TABLE XI_ITEM ADD
  FOREIGN KEY (XDHID) REFERENCES XH_HDR (XDHID)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;


Inserting 100 rows into XH_HDR
- Ascending numbered XDHID
- 10 Rows of them XDST = '6', Rest any value
=> Total 100 Rows


Inserting for every row of XH_HDR 500 rows into XI_ITEM
- XDHID from header table
- Ascending numbered XITEMNO
=> Total 50'000 Rows


In the Execution plan statistics, I expect now 50'010 accesses (50'000 to XI_ITEM, 10 by Index to XH_HDR),
but it shows 50'000 to XI_ITEM and 50'000 by Index to XH_HDR.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Smirnoff Serg added a comment - 08/May/11 09:51 AM
ohenri,

How did you expect 10 reads on table XH_HDR, there's no index on field XDST specified?