Re: cross table indexes or something?

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 <jeremiah(at)cs(dot)earlham(dot)edu>