| Subject | Firebird 3 execution plan |
|---|---|
| Author | liviuslivius |
| Post date | 2017-02-16T18:20:41Z |
Hi,
Firebird3
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE S.ID=1
ORDER BY SD.ID DESC
plan is "wrong"
PLAN SORT (JOIN (S INDEX (PK_SENSOR), SD INDEX (FK_SENSOR_DATA__SENSOR)))
Executing statement...
Statement executed (elapsed time: 0.000s).
64355 fetches, 12 marks, 32 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 51195 index, 10343 seq.
Delta memory: 484688 bytes.
Total execution time: 0.311s
Script execution finished.
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE SD.ID_SENSOR=1
ORDER BY SD.ID DESC
plan is "wrong"
PLAN SORT (JOIN (S INDEX (PK_SENSOR), SD INDEX (FK_SENSOR_DATA__SENSOR)))
Executing statement...
Statement executed (elapsed time: 0.000s).
52954 fetches, 2 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 50990 index, 0 seq.
Delta memory: 0 bytes.
Total execution time: 0.211s
Script execution finished.
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
LEFT JOIN SENSOR S ON S.ID=SD.ID_SENSOR
WHERE SD.ID_SENSOR=1
ORDER BY SD.ID DESC
plan is "expected"
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR))
Executing statement...
Statement executed (elapsed time: 0.000s).
60 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 10 index, 0 seq.
Delta memory: -11992 bytes.
Total execution time: 0.023s
Script execution finished.
SELECT FIRST 1 S.SENSOR_UID, S.NAZWA, SD.ID, SD.ID_SENSOR, SD.DT, SD.WARTOSC, SD.FLAGS
FROM SENSOR_DATA SD
INNER JOIN SENSOR S ON S.ID=SD.ID_SENSOR+0
WHERE S.ID=1
ORDER BY SD.ID DESC
plan is "expected"
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID, S INDEX (PK_SENSOR))
Executing statement...
Statement executed (elapsed time: 0.000s).
173 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 19 index, 0 seq.
Delta memory: -146056 bytes.
Total execution time: 0.023s
Script execution finished.
can this plan be selected by Firebird in somehow automatically without going to left join or +0 solution?
regards,
Karol Bieniaszewski