search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.23k stars 213 forks source link

WARNING:index ... cannot be used in the specified plan #7841

Open skopaliks opened 10 months ago

skopaliks commented 10 months ago

FB 4.0.4 Win64 super server.

Simple test case:

  1. Create empty DB
  2. Run this SQL:

CREATE TABLE tp( id INTEGER); CREATE DESC INDEX tpid_desc ON tp(id);

SET TERM ^; CREATE PROCEDURE tpget AS BEGIN FOR SELECT id FROM tp PLAN (tp ORDER tpid_desc) ORDER BY id DESC AS CURSOR C1 DO BEGIN END END ^ SET TERM ;^ COMMIT;

  1. Do backup
  2. Do restore

Restore will show this warning: gbak:creating indexes gbak:committing metadata gbak: WARNING:index TPID_DESC cannot be used in the specified plan gbak: activating and creating deferred index TPID_DESC gbak:adjusting views dbkey length

dyemanov commented 10 months ago

Seems related to #7499, changed in v4.0.3.

GrigorashPasha commented 10 months ago

The same warning message appears when restoring the database. The current plan - (PLAN SORT (T INDEX (SDOE0006_ID))), is explicitly specified in the procedure query. If it is removed or commented out, the warning does not appear during database restoration. However, even if the explicitly specified plan is removed from the query, it is still used in the Plan Analyzer (IBExpert is used for verification). It is also worth noting that the procedure with this plan works correctly. Tests have been conducted on other versions of Firebird, and this warning occurs in versions 4.0.4, 4.0.3, and 3.0.11. The question arises whether this is an gbak error or if the explicitly specified plan needs to be removed from the queries. Should this warning be ignored, or should the explicitly specified plan be removed from the query?

dyemanov commented 10 months ago

This warning is harmless in this case and can be safely ignored.

GrigorashPasha commented 10 months ago

Thank you for your response. Can you please advise if this issue will be fixed in upcoming versions?

skopaliks commented 10 months ago

From our experience, some times is relevant (we have one case with old DB from pre FB2.5), but is frustrating process to check all cases. What will be also helpful, to show this warnings after index restoration and also show object type and name. Like in trigger xxx index yyy cannot be used in ...