ORDER BY clause on compound index may disable usage of other indices
--------------------------------------------------------------------
Key: CORE-5795
URL: http://tracker.firebirdsql.org/browse/CORE-5795
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.3, 3.0.4
Reporter: Karol Bieniaszewski
Attachments: TESTPLAN.7z
This is releated to old CORE-5020 but now i have reproductible test case with
sample database
restore attached database backup
run the query
SELECT
W.DYR_ID
, W.INSP_ID
, W.KONTO_ID
, W.WPLATA_DATA_WYCIAGU
, W.WPLATA_KONTRAHENT_ID
FROM
WPLATA W
WHERE
W.WPLATA_KONTRAHENT_ID IN (136804)
AND W.DYR_ID = 9
ORDER BY
W.DYR_ID
, W.INSP_ID
, W.KONTO_ID
, W.WPLATA_DATA_WYCIAGU
, W.WPLATA_NR_WYCIAGU
, W.WPLATA_NR_POZYCJI
-------------------------------------------------
PLAN (W ORDER WPLATA_PK)
Executing statement...
Statement executed (elapsed time: 0.000s).
679228 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 334971 index, 0 seq.
Delta memory: 248896 bytes.
Total execution time: 0.500s
Script execution finished.
------------------------------------------------
plan should be
PLAN (W INDEX (IXA_WPLATA__KONTRAHENT__PK))
Executing statement...
Statement executed (elapsed time: 0.000s).
19 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 6 index, 0 seq.
Delta memory: 58112 bytes.
Total execution time: 0.063s
Script execution finished.
or
-----------------------------------------------
PLAN (W ORDER WPLATA_PK INDEX (IXA_WPLATA__KONTRAHENT__PK))
Executing statement...
Statement executed (elapsed time: 0.000s).
185 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 9 index, 0 seq.
Delta memory: 59264 bytes.
Total execution time: 0.047s
Script execution finished.
--
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
