Re: Speeding up Query

Alexander Lohse <al(at)humantouch(dot)de> writes:
> ...
> INTERSECT

> select events.id from events,event_ref,teams,orgs,pers where 1=1 and

> (lower(events.head) like '%web%'
> or lower(events.search) like '%web%'
> or lower(events.ort) like '%web%'
> or lower(events.text) like '%web%'
> or (events.id = event_ref.event_id and event_ref.ref_id = teams.id
> and lower(teams.name) like '%web%')
> or (events.id = event_ref.event_id and event_ref.ref_id = orgs.id
> and lower(orgs.name) like '%web%')
> or (events.id = event_ref.event_id and event_ref.ref_id = orgs.id
> and event_ref.ref_name = 'loc' and lower(orgs.ort) like '%web%')
> or (events.id = event_ref.event_id and event_ref.ref_id = pers.id
> and (lower(pers.name) like '%web%' or lower(pers.prename) like
> '%web%')))

This is pretty horrid: you are generating a cross product of
events * event_ref * teams * orgs * pers and then selecting rows
multiple times out of that very large set. No wonder you lost
patience even with a small test database. I think you wanted
something like

...
INTERSECT (

select events.id from events where
(lower(events.head) like '%web%'
or lower(events.search) like '%web%'
or lower(events.ort) like '%web%'
or lower(events.text) like '%web%'

union

select teams.id from teams where
lower(teams.name) like '%web%'

union

select orgs.id from orgs where
lower(orgs.name) like '%web%'

...

)

This is assuming that the match against event_ref isn't really
necessary, but if it is, you could make each component select be
a two-way join between event_ref and the other table.

regards, tom lane