Thread: 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
Alexander Lohse <al@humantouch.de> writes:
> Would it also possible to make multiple INTERSECTS?
> How do these operate on each other, do I also use parentheses?
Yes, if you don't want to think hard about what the precedence is
(I don't recall either) ...
regards, tom lane