Thank you for the reply. I searched a lot of groups and sites looking for
the way to specify a plan and had no luck. A hint in the right direction?

Kevin Herrmann

...... Original Message .......

On 4 Feb 2005 01:51:21 -0000 firebird-support@yahoogroups.com wrote:
>It is using the doc_type index to speed up the "where" clause. You
>can instruct FB on which plan to use by including it before
>the "order by" if you think that is a good idea.
>
>Adam
>
>
>--- In firebird-support@yahoogroups.com, "Kevin Herrmann"
><kevinherrmann@y...> wrote:
>>
>> 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);
>
>
>
>
>
>________________________________________________________________________
>___________