Re: cross table indexes or something? - Mailing list pgsql-performance

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_actorwhere 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_idand 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=7loops=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)
(actualtime=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)
(actualtime=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)
(actualtime=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=0loops=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 <jeremiah@cs.earlham.edu>