On Wed, 2003-11-26 at 16:32, Hannu Krosing wrote:
> Jeremiah Jahn kirjutas K, 26.11.2003 kell 22:14:
> > I was wondering if there is something I can do that would act similar to
> > a index over more than one table. 
> > 
> > I have about 3 million people in my DB at the moment, they all have
> > roles, and many of them have more than one name. 
> > 
> > for example, a Judge will only have one name, but a Litigant could have
> > multiple aliases. Things go far to slow when I do a query on a judge
> > named smith.
> 
> If you dont need all the judges named smith you could try to use LIMIT.
Unfortunately I do need all of the judges named smith.


> 
> Have you run ANALYZE ? Why does DB think that there is only one judge
> with name like SMITH% ?
I've attached the Analyze below. I have no idea why the db thinks there
is only 1 judge named simth. Is there some what I can inform the DB
about this. In actuality, there aren't any judges named smith at the
moment, but there are 22K people named smith.


> 
> -------------
> Hannu
> 
> P.S. 
> Always send EXPLAIN ANALYZE output if asking for advice on [PERFORM]
 EXPLAIN ANALYZE select distinct 
actor.actor_id,court.id,court.name,role_class_code,full_name from 
actor,identity,court,event,event_actor where role_class_code = 'Judge' and full_name 
like 'SMITH%' and identity.actor_id = actor.actor_id and identity.court_ori = 
actor.court_ori and actor.court_ori = court.id and actor.actor_id = 
event_actor.actor_id and event_actor.event_id = event.event_id and event_date_time > 
'20021126' order by full_name;
                                                                              QUERY 
PLAN                                                                                   
                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=686.42..686.44 rows=1 width=92) (actual time=111923.877..111923.877 
rows=0 loops=1)
   ->  Sort  (cost=686.42..686.43 rows=1 width=92) (actual time=111923.873..111923.873 
rows=0 loops=1)
         Sort Key: identity.full_name, actor.actor_id, court.id, court.name, 
actor.role_class_code
         ->  Nested Loop  (cost=8.45..686.41 rows=1 width=92) (actual 
time=111923.836..111923.836 rows=0 loops=1)
               ->  Nested Loop  (cost=8.45..680.57 rows=1 width=144) (actual 
time=109958.426..111157.822 rows=2449 loops=1)
                     ->  Hash Join  (cost=8.45..9.62 rows=1 width=117) (actual 
time=109945.754..109945.896 rows=6 loops=1)
                           Hash Cond: (("outer".id)::text = ("inner".court_ori)::text)
                           ->  Seq Scan on court  (cost=0.00..1.10 rows=10 width=34) 
(actual time=0.015..0.048 rows=10 loops=1)
                           ->  Hash  (cost=8.45..8.45 rows=1 width=109) (actual 
time=109940.161..109940.161 rows=0 loops=1)
                                 ->  Nested Loop  (cost=0.00..8.45 rows=1 width=109) 
(actual time=10.367..109940.079 rows=7 loops=1)
                                       Join Filter: (("outer".court_ori)::text = 
("inner".court_ori)::text)
                                       ->  Index Scan using name_speed on identity  
(cost=0.00..3.01 rows=1 width=59) (actual time=10.202..238.497 rows=22436 loops=1)
                                             Index Cond: (((full_name)::text >= 
'SMITH'::character varying) AND ((full_name)::text < 'SMITI'::character varying))
                                             Filter: ((full_name)::text ~~ 
'SMITH%'::text)
                                       ->  Index Scan using actor_speed on actor  
(cost=0.00..5.42 rows=1 width=50) (actual time=4.883..4.883 rows=0 loops=22436)
                                             Index Cond: (("outer".actor_id)::text = 
(actor.actor_id)::text)
                                             Filter: ((role_class_code)::text = 
'Judge'::text)
                     ->  Index Scan using event_actor_speed on event_actor  
(cost=0.00..655.59 rows=1229 width=73) (actual time=11.815..198.759 rows=408 loops=6)
                           Index Cond: ((event_actor.actor_id)::text = 
("outer".actor_id)::text)
               ->  Index Scan using event_pkey on event  (cost=0.00..5.83 rows=1 
width=52) (actual time=0.308..0.308 rows=0 loops=2449)
                     Index Cond: (("outer".event_id)::text = (event.event_id)::text)
                     Filter: (event_date_time > '20021126'::bpchar)
 Total runtime: 111924.833 ms
(23 rows)



> 
> -------------
> Hannu
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-- 
Jeremiah Jahn <[EMAIL PROTECTED]>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to