Speeding up Query

Speeding up Query

Post by Alexander Loh » Tue, 15 May 2001 18:24:57



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

http://www.humantouch.de

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

Speeding up Query

Post by Tom La » Wed, 16 May 2001 00:39:33



> ...
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Speeding up Query

Post by Alexander Loh » Wed, 16 May 2001 01:07:33


Quote:>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

http://www.humantouch.de

---------------------------(end of broadcast)---------------------------

 
 
 

Speeding up Query

Post by Tom La » Wed, 16 May 2001 01:10:30



> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

1. NT speeds-ups?

We have a client running on NT and an Online 7.2 server running on UNIX
over a slow WAN. We need to squeeze every bit of performance we can out
of the communications link. We are doing the obvious things to ensure
the database runs fast and trying to move some processing into stored
procedures. Replication is not an option due to support and maintenance
cost and purchase expense of additional equipment, and culture of
centralized data management.

Therefore, has anyone got any tips about:

1) Reducing the latency in the client/server connection.
2) Packing more bits into the same space on the line?

Things we have thought of:

1) Larger TCP/IP buffers on NT. -- Can this be done? how?
2) Using more compact datatypes.
3) Ensuring we are doing select col1...coln and not select *.

Any other ideas? and successful results that can be shared?

2. Update big tables !

3. Multiple query lock-ups

4. Change a User Name

5. Speed, speed, speed...

6. error 7399 when creating linked server

7. Speed, speed, speed, and Sybase disk limitations

8. Interbase-SQL Server 6.5 Conversion Problems

9. Speed, Speed, Speed, and a ramdisk too

10. VB Query vs SQL Query Analyzer Speed?

11. Speed problems with user defined function in a query

12. Query Update Speed

13. Revised embarassing but necessary newbie question(query speed)