Re: Dealing with complex queries

Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com> Wrote
>>Try creating an index on the substrings: you will need
>>to wrap the substring in a function marked "immutable"
>>(or "with (iscachable)" for versions prior to 7.3) for
....

Bruno Wolff III <bruno(at)wolff(dot)to> wrote
>>I think you might be able to coerce use of an
>>index by using like and only using substring
>>on one side.
...

Sorry for the delay. I wanted to give a response after I had tried
different approaches.

Two points worth noting.
The first speedup I experience with the query was by changing
jc.type = 'j' and
pe.type = 'j' and
ppl.type= 'j' and

to

jc.type = 'j' and
pe.type = jc.type and
ppl.type= jc.type and

Didn't measure specific numbers, but the query went from something like 10
minutes to under a minute.

The second point is that after I did a vacuum full that night the query
was even faster to the point that it was less than 10 seconds.

Even though after the vacuum full this query is now speedy I wonder if
there is any suggested numbers of when one should break a query. Was the
query I posted within the parameters of what the server should be able to
handle easily?

What I am wondering is whether there is a number others have bumped into
which are problematic for the server. For example along the lines of "10
tables and 30 conditions". Pulled those numbers out of thin air just to
show the type of sentence, not that I have bumped into any limits myself.
The query I posted is about as complex as I have written them.

For reference my final query was:
select jc.type, jc.jc_id,
jc.last_name, jc.first_name,
jc.track, jc.date,
jc.race, jc.day,
ppl.carried_as, pe.jc_id
from jc_people jc, hraces hr,
rkeys rk, pplkeys ppl,
people pe
where jc.type = 'j' and
pe.type = jc.type and
ppl.type= jc.type and
pe.jc_id = 0 and
pe.ppl_key = ppl.ppl_key and
jc.track = rk.track and
jc.date = rk.date and
jc.race = rk.race and
hr.race_key = rk.race_key and
ppl.ppl_key = hr.jockey_key and
substring(ppl.carried_as from 1 for 3)
= substring(jc.last_name from 1 for 3)
limit 200;