|
If you were logged in you would be able to see more operations.
|
|
|
|
Issue Links:
|
Relate
|
|
This issue relate to:
|
|
CORE-4428
Make optimizer to consider MIN/MAX optimization when making decision about join order
|
|
|
|
|
|
This issue is related to:
|
|
|
|
|
|
|
|
| QA Status: |
Done successfully
|
| Test Details: |
in 2.1 and 2.5: PLAN SORT (JOIN (M NATURAL, D INDEX (THORSES_COLOR_ID))) -- doesn`t see 'rows 1'.
in 3.0 plan should CHANGE and take in account 'rows N' limit.
in 2.1 and 2.5: PLAN SORT (JOIN (M NATURAL, D INDEX (THORSES_COLOR_ID))) -- doesn`t see 'rows 1'.
in 3.0 plan should CHANGE and take in account 'rows N' limit.
|
|
When only limited number of rows is requested (using FIRST/ROW) from ordered set, fetching records in index order is preferable to sort. Optimizer should prefer join order that will allow using navigational index.
Just an idea: Introducing fixed cardinality of result set forced by FIRST/ROWS into calculations may solve this issue naturally.
Original description:
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
plan should be like this
PLAN JOIN (H ORDER PK_HIST_DB__ID,A INDEX (PK_ADRESY_DB__ID))
|
|
Description
|
When only limited number of rows is requested (using FIRST/ROW) from ordered set, fetching records in index order is preferable to sort. Optimizer should prefer join order that will allow using navigational index.
Just an idea: Introducing fixed cardinality of result set forced by FIRST/ROWS into calculations may solve this issue naturally.
Original description:
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
plan should be like this
PLAN JOIN (H ORDER PK_HIST_DB__ID,A INDEX (PK_ADRESY_DB__ID))
|
Show » |
made changes - 28/Sep/07 06:30 AM
| Field |
Original Value |
New Value |
|
Description
|
i have problem with Firebird 1.5.4 and Select First
i do something like that
SELECT FIRST 10 ID, NAME FROM TABLEX
and tableX have 1 000 000 records
this query run in 10 minutes!
definition of table
"ID" INTEGER CONSTRAINT "NK_HIST_DB__ID" NOT NULL,
"LICZNIK" VARCHAR(20),
"REKORD" VARCHAR(20),
"OBIEKT" VARCHAR(10),
"KOD" VARCHAR(10),
"SYSTEM" VARCHAR(5),
"KANAL" VARCHAR(2),
"DATAP" DATE,
"CZASP" VARCHAR(8),
"DATAZ" DATE,
"CZASZ" VARCHAR(8),
"KODELEM" VARCHAR(20),
"ELEMENT" VARCHAR(50),
"STAN" VARCHAR(50),
"ALARM" VARCHAR(15),
"ZRODLO" VARCHAR(10),
"PRIORYTET" VARCHAR(1),
"ZNACZNIK" VARCHAR(1),
"RAPORT" VARCHAR(20),
"RET1" VARCHAR(4),
"RET2" VARCHAR(4),
"RET3" VARCHAR(4),
"RET4" VARCHAR(4),
"RET5" VARCHAR(4),
"RODZAJ" VARCHAR(20),
"RODZAJSTAN" VARCHAR(20),
"ID_OBJ" INTEGER,
CONSTRAINT "PK_TABLE_X__ID" PRIMARY KEY ("ID")
|
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
|
made changes - 28/Sep/07 07:41 AM
|
Summary
|
SELECT FIRST and big table - geting few records take long time
|
SELECT FIRST and big table - geting few records take long time - wrong plan
|
|
Description
|
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
|
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
plan should be like this
PLAN JOIN (H ORDER PK_HIST_DB__ID,A INDEX (PK_ADRESY_DB__ID))
|
made changes - 28/Sep/07 07:48 AM
|
Status
|
Open
[ 1
]
|
Closed
[ 6
]
|
|
Resolution
|
|
Won't Fix
[ 2
]
|
made changes - 01/Oct/07 08:16 AM
|
Resolution
|
Won't Fix
[ 2
]
|
|
|
Status
|
Closed
[ 6
]
|
Reopened
[ 4
]
|
made changes - 01/Oct/07 08:25 AM
|
Environment
|
WINDOWS 2003 SERVER on Server side and WinXP Prof on Client side
|
|
|
Summary
|
SELECT FIRST and big table - geting few records take long time - wrong plan
|
Make optimizer to consider ORDER BY optimization when making decision about join order
|
|
Affects Version/s
|
|
2.0.3
[ 10200
]
|
|
Affects Version/s
|
|
2.1.0
[ 10041
]
|
|
Description
|
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
plan should be like this
PLAN JOIN (H ORDER PK_HIST_DB__ID,A INDEX (PK_ADRESY_DB__ID))
|
When only limited number of rows is requested (using FIRST/ROW) from ordered set, fetching records in index order is preferable to sort. Optimizer should prefer join order that will allow using navigational index.
Just an idea: Introducing fixed cardinality of result set forced by FIRST/ROWS into calculations may solve this issue naturally.
Original description:
i have problem with Firebird 1.5.4 and Select First
i do something like that
select
FIRST 1
*
FROM
HIST_DB H
INNER JOIN ADRESY_DB A ON A.ID=H.ID_OBJ
ORDER BY H.ID ASC
PLAN SORT (JOIN (A NATURAL,H INDEX (FK_HIST_DB__ID_OBJ)))
and HIST_DB have 1 000 000 records
ADRESY_DB have 100 000 records
this query run in 10 minutes!
plan should be like this
PLAN JOIN (H ORDER PK_HIST_DB__ID,A INDEX (PK_ADRESY_DB__ID))
|
|
Priority
|
Major
[ 3
]
|
Minor
[ 4
]
|
made changes - 14/Oct/07 08:17 AM
|
Assignee
|
|
Dmitry Yemanov
[ dimitr
]
|
made changes - 28/Jan/08 01:17 PM
|
Workflow
|
jira
[ 13190
]
|
Firebird
[ 13975
]
|
made changes - 06/Dec/13 05:24 PM
|
Status
|
Reopened
[ 4
]
|
In Progress
[ 3
]
|
made changes - 05/Jan/14 07:37 PM
|
Status
|
In Progress
[ 3
]
|
Open
[ 1
]
|
made changes - 05/Jan/14 07:40 PM
|
Status
|
Open
[ 1
]
|
Resolved
[ 5
]
|
|
Fix Version/s
|
|
3.0 Beta 1
[ 10332
]
|
|
Resolution
|
|
Fixed
[ 1
]
|
made changes - 15/May/14 09:51 AM
|
Comment
|
[ it works for order by ... rows, but for some reason does not apply to the min / max
select min(t1.id)
from t1
join t2 on t1.id = t2.t1_id;
Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan
MIN
============
1
Current memory = 11621440
Delta memory = 547712
Max memory = 11672536
Elapsed time= 3.302 sec
Buffers = 2048
Reads = 11421
Writes = 0
Fetches = 6026958 <-- no good
vs
select min(t2.id)
from t1
join t2 on t1.id = t2.t1_id;
Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Access By ID
-> Index "PK_T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan
MIN
============
1
Current memory = 11840168
Delta memory = 1672
Max memory = 11872248
Elapsed time= 0.058 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 38 <-- good
vs
same as 1 query, but much faster
select t1.id
from t1
join t2 on t1.id = t2.t1_id
order by t1.id rows 1;
Select Expression
-> First N Records
-> Nested Loop Join (inner)
-> Table "T1" Access By ID
-> Index "PK_T1" Full Scan
-> Filter
-> Table "T2" Access By ID
-> Bitmap
-> Index "FK_T2_T1" Range Scan (full match)
ID
============
1
Current memory = 11933608
Delta memory = 52288
Max memory = 11933760
Elapsed time= 0.062 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 137 <-- good
]
|
|
made changes - 28/May/15 03:01 PM
|
Status
|
Resolved
[ 5
]
|
Resolved
[ 5
]
|
|
Test Details
|
|
in 2.1 and 2.5: PLAN SORT (JOIN (M NATURAL, D INDEX (THORSES_COLOR_ID))) -- doesn`t see 'rows 1'.
in 3.0 plan should CHANGE and take in account 'rows N' limit.
|
|
QA Status
|
|
Done successfully
|
made changes - 28/Sep/15 01:09 PM
|
Link
|
|
This issue is related to QA-628
[ QA-628
]
|
|
Improvement
Resolved
Minor
