Consider equivalence classes for index navigation
-------------------------------------------------
Key: CORE-5676
URL: http://tracker.firebirdsql.org/browse/CORE-5676
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Dmitry Yemanov
Priority: Minor
Test case:
recreate table document(id int primary key using index pk_document);
recreate table doc_ip_doc(id int primary key using index pk_doc_ip_doc, name
varchar(100));
insert into document (id) select row_number() over() from rdb$types, rdb$types;
insert into doc_ip_doc (id) select row_number() over() from rdb$types;
commit;
set planonly;
select document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by doc_ip_doc.id;
--PLAN JOIN (DOC_IP_DOC ORDER PK_DOC_IP_DOC, DOCUMENT INDEX (PK_DOCUMENT))
select document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by document.id;
--PLAN SORT (JOIN (DOC_IP_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT)))
select doc_ip_doc.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by id;
--PLAN JOIN (DOC_IP_DOC ORDER PK_DOC_IP_DOC, DOCUMENT INDEX (PK_DOCUMENT))
select document.id, doc_ip_doc.name
from doc_ip_doc
join document on document.id=doc_ip_doc.id
order by id;
--PLAN SORT (JOIN (DOC_IP_DOC NATURAL, DOCUMENT INDEX (PK_DOCUMENT)))
All queries are semantically the same and should have the same plan (ORDER
rather than SORT), but the optimizer understands only explicit reference inside
ORDER BY clause and ignores sorts derived from equivalent expressions.
--
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
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
