This query is horribly slow on Firebird 1.0.3 UNTIL I remove the
'order by'. I think I've included all things I need. Here is the
relevant Info:

select d.*, e.NAME, e.GIVENNAME, a.EVENTTYPE from documents d
left join entity e on d.subject_ID=e.id
left join auditmessage a on d.id = a.id1 and a.eventtype = 19
where d.doc_type in (5,6,7)
and e.status=0
and e.name <> 'MACRO'
and e.AttendingID=2
and a.ID1 is null
order by d.doc_category;

There is an index on doc_category, but the optimizer doesn't want to
use it. Here is the Plan:

PLAN SORT (JOIN (JOIN (D INDEX
(DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
(RDB$PRIMARY8)),A INDEX (AUDITMESSAGE_EVENTTYPE_X,AUDITMESSAGE_ID1_X)))

Adapted Plan
PLAN SORT (JOIN (JOIN (D INDEX
(DOCUMENTS_TYP_X,DOCUMENTS_TYP_X,DOCUMENTS_TYP_X),E INDEX
(INTEG_36)),A INDEX (AUDITMESSAGE_EVENTTYPE_X,AUDITMESSAGE_ID1_X)))


Here is the DDL:

CREATE TABLE DOCUMENTS (
ID INTEGER NOT NULL,
DOC_TYPE SMALLINT NOT NULL,
SUBJECT_ID INTEGER,
SESSION_ID INTEGER,
CREATED DATE NOT NULL,
METADOCUMENT_ID INTEGER,
ATTRIBUTES INTEGER,
DESCRIPTION VARCHAR(250),
DOC_CATEGORY SMALLINT
);
ALTER TABLE DOCUMENTS ADD PRIMARY KEY (ID);
CREATE INDEX DOCUMENTS_CAT_X ON DOCUMENTS (DOC_CATEGORY);
CREATE INDEX DOCUMENTS_SID_X ON DOCUMENTS (SUBJECT_ID);
CREATE INDEX DOCUMENTS_TYP_X ON DOCUMENTS (DOC_TYPE);

CREATE TABLE ENTITY (
ID ENIGMA_ID /* INTEGER NOT NULL */ NOT NULL,
ENTITYTYPE SMALLINT NOT NULL,
NAME VARCHAR(48) NOT NULL,
ROLE_IDS VARCHAR(64),
BIRTH DATE,
MRNUM VARCHAR(16),
SS CHAR(9),
SEX CHAR(1),
RACE VARCHAR(16),
STATUS INTEGER,
PAGER VARCHAR(20),
EMAIL VARCHAR(48),
TITLE VARCHAR(48),
DEPARTMENT VARCHAR(48),
COMPANY VARCHAR(48),
ISPATIENT CHAR(1),
ISEMPLOYEE CHAR(1),
ISPHYSICIAN CHAR(1),
PHOTO BLOB SUB_TYPE -3 SEGMENT SIZE 1024,
HOME_ID ENIGMA_ID /* INTEGER NOT NULL */,
WORK_ID ENIGMA_ID /* INTEGER NOT NULL */,
DEA VARCHAR(20),
AMA VARCHAR(20),
PREFIX SMALLINT,
SUFFIX CHAR(16),
IVERSION SMALLINT NOT NULL,
IOBSOLETE CHAR(1),
CLASSSIGNATURE VARCHAR(64),
PHYSICIANS VARCHAR(128),
USEROID INTEGER,
ATTENDINGID INTEGER,
MIDDLE VARCHAR(16),
OTHERMRNUMS VARCHAR(40),
NOTEBOOK VARCHAR(2048),
PHARMACY_NAME VARCHAR(64),
PHARMACY_PHONE CHAR(20),
PHARMACY_FAX CHAR(20),
USER_SETTINGS BLOB SUB_TYPE 0 SEGMENT SIZE 80,
ISGUARANTOR CHAR(1),
GUARANTORID INTEGER,
GUARANTORRELATIONSHIP CHAR(32),
INSURANCEID_1 INTEGER,
INSURANCEID_2 INTEGER,
INSURANCEID_3 INTEGER,
SIGNATURE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
GIVENNAME VARCHAR(64)
);
ALTER TABLE ENTITY ADD PRIMARY KEY (ID);
CREATE INDEX ENTITY_ATTENDING ON ENTITY (ATTENDINGID);
CREATE INDEX ENTITY_GIVENNAME ON ENTITY (GIVENNAME);
CREATE INDEX ENTITY_NAME ON ENTITY (NAME);
CREATE INDEX ENTITY_ROLE ON ENTITY (ISPATIENT, ISEMPLOYEE, ISPHYSICIAN);
CREATE INDEX ENTITY_STATUS ON ENTITY (STATUS);
CREATE INDEX ENTITY_USEROID ON ENTITY (USEROID);

CREATE TABLE AUDITMESSAGE (
EVENTTYPE SMALLINT,
EVENTDATE DATE,
USERNAME CHAR(16),
ID1 INTEGER,
ID2 INTEGER,
AUDITTEXT VARCHAR(255)
);
CREATE INDEX AUDITMESSAGE_EVENTDATE2_X ON AUDITMESSAGE (EVENTDATE);
CREATE DESCENDING INDEX AUDITMESSAGE_EVENTDATE_X ON AUDITMESSAGE
(EVENTDATE);
CREATE INDEX AUDITMESSAGE_EVENTTYPE_X ON AUDITMESSAGE (EVENTTYPE);
CREATE INDEX AUDITMESSAGE_ID1_X ON AUDITMESSAGE (ID1);
CREATE INDEX AUDITMESSAGE_USERNAME_X ON AUDITMESSAGE (USERNAME);