Alexander Lohse <al@humantouch.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
