Issue Details (XML | Word | Printable)

Key: CORE-1482
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Dmitry Yemanov
Reporter: Karol Bieniaszewski
Votes: 2
Watchers: 2
Operations

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

Make optimizer to consider ORDER BY optimization when making decision about join order

Created: 28/Sep/07 03:44 AM   Updated: 28/Sep/15 01:09 PM
Component/s: Engine
Affects Version/s: 1.5.4, 2.0.3, 2.1.0
Fix Version/s: 3.0 Beta 1

Issue Links:
Relate

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.


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



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski 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!
Karol Bieniaszewski 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))

Pavel Cisar made changes - 28/Sep/07 07:48 AM
Status Open [ 1 ] Closed [ 6 ]
Resolution Won't Fix [ 2 ]
Pavel Cisar made changes - 01/Oct/07 08:16 AM
Resolution Won't Fix [ 2 ]
Status Closed [ 6 ] Reopened [ 4 ]
Pavel Cisar 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 ]
Dmitry Yemanov made changes - 14/Oct/07 08:17 AM
Assignee Dmitry Yemanov [ dimitr ]
Pavel Cisar made changes - 28/Jan/08 01:17 PM
Workflow jira [ 13190 ] Firebird [ 13975 ]
Dmitry Yemanov made changes - 06/Dec/13 05:24 PM
Status Reopened [ 4 ] In Progress [ 3 ]
Dmitry Yemanov made changes - 05/Jan/14 07:37 PM
Status In Progress [ 3 ] Open [ 1 ]
Dmitry Yemanov made changes - 05/Jan/14 07:40 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.0 Beta 1 [ 10332 ]
Resolution Fixed [ 1 ]
Dmitry Yemanov made changes - 15/May/14 09:50 AM
Link This issue relate to CORE-4428 [ CORE-4428 ]
Dmitry Yemanov 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
]
Pavel Zotov 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
Pavel Cisar made changes - 28/Sep/15 01:09 PM
Link This issue is related to QA-628 [ QA-628 ]