MCP Firebird 0.4.0: the advisor that knows when not to create an index

MCP Firebird 0.4.0 rewrites the index advisor. It now measures the query before it speaks, proposes the remedy that costs least, knows expression indexes, partial indexes and indexes for the ORDER BY, and when the scan is the right choice it answers with numbers and no SQL to run.

You have a slow query. The plan says PLAN (ORDERS NATURAL), meaning a full scan of the ORDERS table, and there is no index on the filtered column. The advice you get, from a human, from an AI, or from a Sunday DBA, is almost always the same: create the index.

Look at the numbers, though. That scan reads 20,048 records and returns 18,000 of them. 89.8% of what it touches ends up in the result. An index there would make you do two reads instead of one for nearly every row in the table, and you’d pay for it with one extra write on every INSERT, UPDATE and DELETE, from today until the day somebody drops it. You know perfectly well nobody will, because “something might break”. An operation meant to improve performance would end up making it worse, for the entire life of the application.

In this case the scan was the right plan. The right advice was to do nothing, not “create an index”.

MCP Firebird 0.4.0, released on August 7, 2026, teaches fb_suggest_indexes to tell you “this is fine” instead of “create this index”.

In short

• fb_suggest_indexes rewritten around a ladder of remedies, cheapest first. Statistics first, then reactivating a disabled index, and only at the end a brand new one.
• A scan can be the right plan. The query gets measured: if the scan keeps most of what it reads, the answer is numbers and no SQL.
• Three more index shapes: on an expression, partial on Firebird 5.0, and for resolving an ORDER BY.
• The engine explains the plan. On Firebird 3.0 and later you get the “explained” plan: index names and how many segments the query actually reaches.
• All of it in the free edition, read-only, on Firebird from 2.5 to 5.0.
• Repository: github.com/danieleteti/mcp-firebird

If you don’t know the project yet, the introduction article explains what it is and how to install it. In short: an executable written in Delphi that your AI assistant launches on its own and talks to over stdin/stdout, with no services and no open ports.

The remedy ladder

Up through 0.3.x the advisor reasoned from metadata. It found NATURAL in the plan, derived the predicate columns from the SQL, and proposed an index where none existed yet. Given the information it had, that was a reasonable answer, and most of the time it was also the right one.

What stayed outside that reasoning was everything metadata does not contain:

• how many rows does that scan actually select?
• do an index’s statistics still describe today’s data?
• which index shapes does the engine you run in production actually allow, plain B-tree only, or also partial and expression indexes?

You can only answer those by measuring, and to measure you have to run the query. 0.4.0 takes that step.

The rule the advisor follows is this:

The advisor tries the rungs in order of cost and stops at the first one that explains what it saw. SET STATISTICS INDEX refreshes the selectivity of an index you already have: it leaves nothing behind, it’s safe even under concurrent load, and in an embarrassing number of cases it’s all that was needed. ALTER INDEX ... ACTIVE reactivates a disabled index, and you already paid its creation cost a while ago. CREATE INDEX comes last, because it’s the only one of the three you keep paying for on every write for as long as it exists.

The scan that is the right plan

To say “this scan is fine” you need to know how much it produces, and that number doesn’t exist in the metadata. That’s why 0.4.0 runs the query: once, inside the read-only transaction, and only when the plan shows a scan or a sort.

A scan that keeps most of what it reads, above roughly 20% selectivity, is the correct plan. The same goes for a table that fits in a handful of pages. In those cases the answer carries the measurement and no statement to run:

Table ORDERS is scanned when filtered by STATE, and that is the right plan:
the scan returns 89.8% of what it reads (18000 of 20048 records).

Let me state the price upfront: on a large table, a call that used to read only metadata now runs a full scan. That’s the cost of having a number instead of a guess.

Statistics come before structure

Firebird computes an index’s selectivity when you create it, and never recomputes it on its own. The table grows, the distribution changes, and the optimizer keeps planning against an old snapshot. So an index the optimizer ignores might be neglected because of stale statistics, not because it’s useless.

0.4.0 compares the stored selectivity with the actual one before saying an index isn’t needed, and when the two figures diverge it answers SET STATISTICS INDEX. The same check runs inside fb_suggest_index_drops, which now checks the actual selectivity before proposing a DROP INDEX: an index running on stale statistics deserves a refresh, not the bin. There’s also a new goal for optimization_goal, no_stale_index_statistics, the only one of the four whose first move writes nothing structural.

A real session, with Claude Desktop

Examples so far. What follows instead is a real session I ran myself: Claude Desktop, MCP Firebird configured against a Firebird 5.0.4 engine, the repository’s test database. I didn’t reconstruct it for the article, I only trimmed it where it repeated itself.

The conversation below is in English for simplicity: with MCP Firebird the assistant answers in whatever language you write to it, so had I asked the same question in Italian, I would have gotten the answer in Italian.

PLAN (CUSTOMERS NATURAL)

Select Expression
    -> Filter
        -> Table "CUSTOMERS" Full Scan

Full scan: 5004 records read sequentially to return 0 rows (55 pages read, 5437 fetched, 9 ms).

The assistant had already read the table’s structure, and cites it to give the diagnosis some weight:

Indexes: IDX_CUST_CITY (CITY, INACTIVE), IDX_CUST_NAME (NAME), IDX_CUST_NAME_CITY (NAME, CITY), IDX_CUST_STATUS (STATUS), RDB$PRIMARY1 (CUSTOMER_ID, primary key).

Then come two fixes, not just one:

CREATE INDEX IDX_CUSTOMERS_UPPER_NAME ON CUSTOMERS COMPUTED BY (UPPER(NAME));
SET STATISTICS INDEX IDX_CUSTOMERS_UPPER_NAME;

ALTER TABLE CUSTOMERS ALTER NAME TYPE VARCHAR(400)
  CHARACTER SET UTF8 COLLATE UNICODE_CI;

That last line is what convinced me to close the article with this session instead of a made-up example: the tool had solved the technical problem and then stopped to point out that maybe the real problem was something else, the data I was looking for wasn’t there. No tool does that on its own unless you told it to.

Closing, still in the same reply:

Partial indexes and indexes for ORDER BY

There are two more index shapes in 0.4.0. Partial indexes, which Firebird introduced with 5.0: take WHERE STATE = 'ACTIVE' AND TAG = ?. The value of TAG changes on every call, while STATE = 'ACTIVE' is fixed and always selects the same subset of rows. A plain index on TAG indexes the whole table, including the non-ACTIVE rows that the query will never look at. A partial index indexes only the rows you actually need:

CREATE INDEX IDX_CUSTOMERS_ACTIVE_TAG ON CUSTOMERS (TAG) WHERE STATE = 'ACTIVE';

If ACTIVE rows are 3% of the table, that index ends up roughly thirty times smaller, and inserting a non-ACTIVE row doesn’t even touch it. The advisor proposes both shapes, the plain one and the partial one. On Firebird 4.0 and earlier the WHERE clause doesn’t exist, so only the plain index comes back, with a note on what you’d gain by moving to 5.0.

Finally, ORDER BY. fb_analyze_query has flagged external SORT operations since the first release, and now fb_suggest_indexes proposes the index that removes them, with the segments in the right direction, because Firebird cannot walk an ascending index backwards. Ask for ORDER BY a ASC, b DESC and it tells you no single index can serve you, instead of proposing one the engine will ignore.

The engine explains the plan

On Firebird 3.0 and later, fb_analyze_query captures the “explained” plan via SET EXPLAIN ON. Before, the table behind an alias was inferred with a regex over the FROM clause; now the engine itself declares the index names, how much of each index the query actually uses, the join strategy and the alias-to-table map.

With that detail, the advisor finds two problems it couldn’t even see before.

The first is about composite indexes. Say you have an index on (CITY, NAME, STATUS) and a query that filters only on CITY and NAME: the explained plan shows Firebird used only the first two segments of the index, never the third. STATUS gets written into the index on every INSERT and UPDATE, but no query of this shape ever reads it. That’s a write cost buying nothing, and it’s only visible now that you know how many segments the query actually reaches.

The second is about joins. Before, you only saw the overall plan. Now, if the engine fully scans both the left and right table of a join instead of using an index on at least one of them, the advisor tells you, and names the table.

On Firebird 2.5 this part simply doesn’t run: SET EXPLAIN doesn’t exist on that version, and sending it anyway would break the script with a command the engine doesn’t recognize. So there the plan stays the one inferred from the query, as before.

If you forget to give a parameter a value

When you analyze a query with a placeholder, say SELECT * FROM CUSTOMERS WHERE CITY = ?, and don’t pass it a value, fb_analyze_query now refuses to run it and tells you why. Before, it ran it anyway: the parameter ended up bound to NULL, CITY = NULL matches nothing by definition, and the report came back with “0 rows read” over a table scanned from top to bottom, a number that looked like definitive proof you needed an index, when it was actually measuring a different query from the one you meant to test.

Two more minor fixes in the same package. First: an inequality comparison like A <> 5 no longer triggers an index proposal. A B-tree doesn’t help here: to exclude a single value the engine still has to read almost the whole index, so Firebird prefers the scan, and proposing the index would have been wasted effort. Second: the stale-statistics check now covers multi-column indexes too, not just single-column ones.

How to try it

Download MCPFirebird-0.4.0-win64.zip from the GitHub release, copy .env.example to .env, and point firebird.client_lib at the fbclient.dll of your own installation. It’s not in the zip, on purpose: only you know which server you’re talking to. Then register the exe as an stdio MCP server in your agent.

Then put your slowest query in front of it and ask what index it needs. The most interesting answer you can get is “none, and here are the numbers”.

The full changelog lists everything in 0.4.0, and the repository has the advisor’s design doc, with the measurements the thresholds come from.