Re: Speeding up Query

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