Force optimizer to take in account 'order by T.id ROWS n' that is specified
for big table 'T' which is cross joined with small datasource
------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5048
URL: http://tracker.firebirdsql.org/browse/CORE-5048
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 2.5.5, 3.0 RC 1
Reporter: Pavel Zotov
Priority: Minor
DDL:
====
recreate table thuge(id int, x int);
insert into thuge
select row_number()over(), rand()*10000
from rdb$types,rdb$types,rdb$types;
commit;
create index thuge_x on thuge(x);
recreate table ttiny(id int, v int, u int, w int);
insert into ttiny values(1, 100, 200, 300);
create index ttiny_id on ttiny(id);
commit;
Query-1:
=======
select h.id, t.u, t.v, t.w
from (
select h.id
from thuge h
order by x
rows 1
) h
cross join ttiny t;
Its plan:
Select Expression
-> Nested Loop Join (inner)
-> First N Records
-> Table "THUGE" as "H H" Access By ID
-> Index "THUGE_X" Full Scan
-> Table "RDB$DATABASE" Full Scan
This query returns one row instantly (<15ms, Fetches from cache = 18).
Query-2:
=======
select h.id, t.u, t.v, t.w
from thuge h
cross join ttiny t
order by h.x
rows 1;
It has totally inefficient plan:
Select Expression
-> First N Records
-> Sort (record length: 76, key length: 8)
-> Nested Loop Join (inner)
-> Filter
-> Table "TTINY" as "T" Access By ID
-> Bitmap
-> Index "TTINY_ID" Range Scan (lower bound: 1/1,
upper bound: 1/1)
-> Table "THUGE" as "H" Full Scan
Statistics:
Execute : 21 859,00 ms
Fetches: 32 991 255
NR for THuge = 16387064.
But both queries always will return the same data because ORDER BY clause does
not contain any fields from TTINY table.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
