Re: Dealing with complex queries

Jeff Eckermann <jeff_eckermann@yahoo.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@wolff.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;