I could use some guidance on how to optimize a query. The short version is
that I have two queries that run fast, but when I combine them, the result
is slow. The fast queries (with plans from FlameRobin) are:

select * from COESTUD cs where cs.COESEQ=5000
PLAN (CS INDEX (COESTUD_))

select * from VIEWABLE_ENROLLMENTS ve
join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and
ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
where ve.USER_ID=1
PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX
(AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F
NATURAL))
PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR
INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE
VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX
(SCHLHIST_FACILITYID), CS INDEX (COESTUD_SHSEQINDEX)))

They both run in well under .1s but when I combine them it takes over 1.5s:

select * from VIEWABLE_ENROLLMENTS ve
join COESTUD cs on ve.STUDENTSEQ=cs.STUDENTSEQ and ve.DOMID=cs.DOMID and
ve.DBID=cs.DBID and ve.SHSEQ=cs.SHSEQ
where ve.USER_ID=1 and cs.COESEQ=5000
PLAN JOIN (JOIN (SORT (JOIN (JOIN (VE VF U INDEX (USERS_), VE VF AR INDEX
(AGNTROLE_, AGNTROLE_), VE VF ACT INDEX (ACTION__, ACTION__)), VE VF F
NATURAL))
PLAN SORT (JOIN (SORT (JOIN (JOIN (VE VF AL U INDEX (USERS_), VE VF AL AR
INDEX (AGNTROLE_, AGNTROLE_), VE VF AL ACT INDEX (ACTION__, ACTION__)), VE
VF AL D NATURAL)), VE VF F INDEX (FACILITY_DISTRICTCODE))), VE H INDEX
(SCHLHIST_FACILITYID), CS INDEX (COESTUD_)))

Here's the relevant ddl:

CREATE TABLE USERS(
ID integer NOT NULL,
USER_ID varchar(80) COLLATE EN_US,
);
CREATE UNIQUE INDEX USERS_ ON USERS (ID);
CREATE UNIQUE INDEX USERS_USER_ID ON USERS (USER_ID);

CREATE TABLE AGNTROLE(
AGENT varchar(30) COLLATE EN_US,
ROLE_ varchar(30) COLLATE EN_US
);
CREATE UNIQUE INDEX AGNTROLE_ ON AGNTROLE (AGENT,ROLE_);

CREATE TABLE ACTION_(
ROLE_ varchar(30),
CLASS varchar(30),
OBJECT varchar(100),
ACTION_ varchar(30),
GOODUNTIL timestamp
);
CREATE UNIQUE INDEX ACTION__ ON ACTION_ (ROLE_,CLASS,OBJECT,ACTION_);
CREATE INDEX ACTION__OBJECTINDEX ON ACTION_ (OBJECT);

CREATE TABLE FACILITY(
FACILITYID varchar(6) COLLATE EN_US,
DISTRICTCODE integer
);
CREATE UNIQUE INDEX FACILITY_ ON FACILITY (FACILITYID);
CREATE INDEX FACILITY_DISTRICTCODE ON FACILITY (DISTRICTCODE);

CREATE TABLE DISTRICT(
DISTRICTCODE integer
);
CREATE UNIQUE INDEX DISTRICT_ ON DISTRICT (DISTRICTCODE);

CREATE TABLE SCHLHIST(
STUDENTSEQ integer,
DOMID varchar(2) COLLATE EN_US,
DBID smallint,
SHSEQ integer,
FACILITYID varchar(6) COLLATE EN_US
);
CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ);
CREATE INDEX SCHLHIST_FACILITYID ON SCHLHIST (FACILITYID);
CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID);

CREATE TABLE STUDENT(
STUDENTSEQ integer
);
CREATE UNIQUE INDEX STUDENT_ ON STUDENT (STUDENTSEQ);

CREATE TABLE COESTUD(
COESEQ integer,
STUDENTSEQ integer,
DOMID varchar(2) COLLATE EN_US,
DBID smallint,
SHSEQ integer
);
CREATE UNIQUE INDEX COESTUD_ ON COESTUD (COESEQ,STUDENTSEQ);
CREATE INDEX COESTUD_SHSEQINDEX ON COESTUD (STUDENTSEQ,DOMID,DBID,SHSEQ);

create view VIEWABLE_ENROLLMENTS (USER_ID, STUDENTSEQ, DOMID, DBID, SHSEQ)
as
select vf.USER_ID, h.STUDENTSEQ, h.DOMID, h.DBID, h.SHSEQ
from VIEWABLE_FACILITIES vf join SCHLHIST h on vf.FACILITYID=h.FACILITYID

create view VIEWABLE_FACILITIES (USER_ID, FACILITYID) as
select distinct u.ID, f.FACILITYID from USERS u
join AGNTROLE ar on ar.AGENT in (cast(u.ID as varchar(10)), '*')
join ACTION_ act on
ar.ROLE_=act.ROLE_ and act.CLASS in ('FACILITY', '*') and
act.ACTION_ in ('ALTER', 'VIEW', '*') and
coalesce(act.GOODUNTIL, current_timestamp) >= current_timestamp
join FACILITY f on act.OBJECT in (f.FACILITYID, '*')
union
select distinct al.USER_ID, f.FACILITYID from viewable_leas al
join facility f on al.DISTRICTCODE=f.DISTRICTCODE

create view VIEWABLE_LEAS (USER_ID, DISTRICTCODE) as
select distinct u.ID, d.DISTRICTCODE from USERS u
join AGNTROLE ar on ar.AGENT in (cast(u.ID as varchar(10)), '*')
join ACTION_ act on
ar.ROLE_=act.ROLE_ and act.CLASS in ('LEA', '*') and
act.ACTION_ in ('ALTER', 'VIEW', '*') and
coalesce(act.GOODUNTIL, current_timestamp) >= current_timestamp
join DISTRICT d on
act.OBJECT in (cast(d.DISTRICTCODE as varchar(10)), '*')


[Non-text portions of this message have been removed]