Speeding up Query
Hi,
can anyone to me whether it is possible to speed up the following
full text search:
____________________________________
select events.id from events where
(events.aid & 1) <> 0 and
((events.expires = 't' and events.v_date > 989826471) or
(events.expires = 'f'))
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%')))
____________________________________
explain produces:
SetOp Intersect (cost=107135.71..107140.29 rows=183 width=192)
-> Sort (cost=107135.71..107135.71 rows=1830 width=192)
-> Append (cost=0.00..107036.53 rows=1830 width=192)
-> Subquery Scan *SELECT* 1 (cost=0.00..35.00
rows=495 width=12)
-> Seq Scan on events (cost=0.00..35.00
rows=495 width=12)
-> Subquery Scan *SELECT* 2 (cost=0.00..107001.53
rows=1335 width=192)
-> Nested Loop (cost=0.00..107001.53 rows=1335 width=192)
-> Nested Loop (cost=0.00..94.03
rows=1043 width=132)
-> Nested Loop (cost=0.00..3.17
rows=7 width=96)
-> Nested Loop
(cost=0.00..2.03 rows=1 width=60)
-> Seq Scan on teams
(cost=0.00..1.01 rows=1 width=24)
-> Seq Scan on orgs
(cost=0.00..1.01 rows=1 width=36)
-> Seq Scan on event_ref
(cost=0.00..1.07 rows=7 width=36)
-> Seq Scan on pers
(cost=0.00..11.49 rows=149 width=36)
-> Seq Scan on events (cost=0.00..20.00
rows=1000 width=60)
____________________________________
I am not quite shure how to read this as well.
None of the tables is filled with production data yet.
Only table "pers" contains 150 records.
The all the others contain less than 10 testing records.
The weird thing is, that I never get any result! Psql seems to hang!
PHP is also waiting for any answer from pgsql, then breaks up.
Is it a good idea to use lower on "text" columns?
Thanx in advance.
Regards,
Alex
--
___________________________
Human Touch Medienproduktion GmbH
Am See 1
17440 Klein Jasedow
Alexander Lohse
Tel: (038374) 75211
Fax: (038374) 75223
eMail: al@humantouch.de
http://www.humantouch.de
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
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
Hi Tom,
this thing is a bit more complicated.
event_ref is the table containing the relations events <-> teams (One
event/multiple Teams)
Spoken: Teams or Persons organise Events,
that's what: (events.id = event_ref.event_id and event_ref.ref_id =
teams.id) is for.
Now, this search wants to be able to find all events that are
organised by teams, pers, orgs where teams,p,o name contains
"search_string".
But maybe you already brought the idea to me! Instead of using
pleanty of ORs I should try using plenty UNIONs.
Would it also possible to make multiple INTERSECTS?
How do these operate on each other, do I also use parentheses?
I am bit in a hurry, right in the moment that why I write stenograph! ;-)
Thank you in advance,
Alex
--
___________________________
Human Touch Medienproduktion GmbH
Am See 1
17440 Klein Jasedow
Alexander Lohse
Tel: (038374) 75211
Fax: (038374) 75223
eMail: al@humantouch.de
http://www.humantouch.de