1

I am trying understand why Firebird 3 does not use an index in my simplified query below. If I run it without the plan clause it uses

PLAN JOIN (L NATURAL, T INDEX (FK_TRANS_LEDGER), P INDEX (IDX_PRODUCTIONS1))

If I explicitly add the plan with the index (as below) it throws an error saying I cannot use the index in the plan (The index is unique on ledgerkey)

SELECT P.prodname FROM TRANS t 
join productions p on t.jobkey=p.prodkey
join ledger l on l.ledgerkey=t.lkey
PLAN JOIN (L INDEX (IDX_LEDGER4), T INDEX (FK_TRANS_LEDGER), P INDEX (IDX_PRODUCTIONS1))
Arioch 'The
  • 15,799
  • 35
  • 62
Tony
  • 48
  • 1
  • 9
  • You might get a better answer if you ask on firebird-support, but I think this has to do with the fact that a table needs to 'drive' the select, that means that Firebird will need to use one table as the starting point to join the other tables, and therefor needs to perform a table scan on one of the tables involved. – Mark Rotteveel Nov 30 '19 at 09:43
  • why would you want to use index always ? your query hints you want to get ALL the data, not a small subset of it. And then natural scan - sequential reading off HDD, page after page - might be better than indexed random-access back-and-forth magnetic head thrashing. Indexed fetch is almost always better weh you nee a row or two ignoring all others. But the large is the share of the data you are reading - the larger are negative effects of indexed access. – Arioch 'The Nov 30 '19 at 15:59
  • The sql is a simple example to illustrate my issue. My experience is that any join with an index on the key field is much faster with an index on the key field – Tony Nov 30 '19 at 22:34
  • It looks like FB3.0.3 does use the index (my question used FB3.0.1)so maybe the optimiser was updated. – Tony Nov 30 '19 at 22:39
  • @Tony if so you definitely better download `release notes` of the latest (3.0.5 ? 3.0.4? ) and read chapters about version to version enhancements and v-t-v bugfixes. Perhaps you will find something join-related – Arioch 'The Dec 01 '19 at 14:39

0 Answers0