I have the following slow query
SELECT * FROM PSearch WHERE PetName like 'JETHRO%' Order by
PetName,OwnerLName
firebird comes up with this plan for it
PLAN SORT (JOIN (PSEARCH O NATURAL,PSEARCH P INDEX (PETNAMEONLY,OWNERNUM4)))
this plan has an execution time of 00:00:00.0651 with a prepare time of
00:00:00:0010 according to ibconsole,
however if I specify the following plan
PLAN JOIN (PSEARCH P INDEX (PETNAMEONLY),PSEARCH O INDEX (OWNERIDOWNERS))
the plan has an execution time of 00:00:00.0010 and a prepare time of
00:00:00:0000.
all values are on production machine running firebird superserver 1.5.0
on a 2.8 ghz xeon hyperthreading disabled and 2gigs of ram, on the
testing laptop running firebird superserver 1.5.1 with a 1.4ghz centrino
256mb ram same results only the execution time for the slow plan is
higher prepare times are the same and execution time on the fast plan is
the same.
the index ownernum4 is the ownerid index for pets and the index
owneridowners is the ownerid index for owners
in owners owneridowners is unique for every entry however in pets many
pets share the same ownerid.
petnameonly is not unique as this is a listin of 90,000 pets however the
entry jethro% only recieves 8 results, it is one of the more unique
petnames as far as this database goes.
PSearch is a view which is described as follows
select p.OwnerID,
p.PetID,
p.PetName,
p.Alert,
p.RabiesTagNum,
p.XrayNumber,
p.Species,
p.Breed,
p.Color,
p.DateVacc,
p.DateHTWM,
p.IsActive,
p.SpayNeuter,
p.Sex,
p.Weight,
p.DOB,
p.PetNotes,
p.History,
o.OwnerFName || ' ' || o.OwnerLName as FullName,
o.OwnerLName,
o.OwnerFName
from Pets p join Owners o on o.OwnerID = p.OwnerID

rebuilding indexes has no effect, and index selectivity is recalculated
twice a day.
is my only solution to keep specifying the plan whenever this query
comes up? I would love to have some insight as to this query.