Re: cross table indexes or something?

Sybase IQ lets you build "joined indexsets". This is amazing but pricey
and really intended more for Data Warehousing than OLTP, although they did
release a version which permitted writes on-the-fly. (This was implemented
using a multi-concurrency solution much like PostreSQL uses.)

It essentially pre-joined the data.

Marc A. Leith
redboxdata inc.
E-mail:mleith(at)redboxdata(dot)com

Quoting Jeremiah Jahn <jeremiah(at)cs(dot)earlham(dot)edu>:

> 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. Does any one know a possible way to speed this up?
>
> I would think that In a perfect world there would be a way to create an
> index on commonly used joins, or something of that nature. I've tried
> partial indexes, but the optimizer feels that it would be quicker to do
> an index scan for smith% then join using the pkey of the person to get
> their role. For litigants, this makes since, for non-litigants, this
> doesn't.
>
> thanx for any insight,
> -jj-
>
> the basic schema
>
> actor
> actor_id PK
> role_class_code
>
> identity
> actor_id FK
> identity_id PK
> full_name
>
> event
> event_date_time
> event_id PK
>
> event_actor
> event_id FK
> actor_id FK
>
>
> explain 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=726.57..726.58 rows=1 width=92)
> -> Sort (cost=726.57..726.57 rows=1 width=92)
> Sort Key: identity.full_name, actor.actor_id, court.id, court.name,
> actor.role_class_code
> -> Nested Loop (cost=3.02..726.56 rows=1 width=92)
> -> Nested Loop (cost=3.02..720.72 rows=1 width=144)
> -> Nested Loop (cost=3.02..9.62 rows=1 width=117)
> Join Filter: (("outer".court_ori)::text =
> ("inner".court_ori)::text)
> -> Hash Join (cost=3.02..4.18 rows=1 width=93)
> Hash Cond: (("outer".id)::text =
> ("inner".court_ori)::text)
> -> Seq Scan on court (cost=0.00..1.10
> rows=10 width=34)
> -> Hash (cost=3.01..3.01 rows=1 width=59)
> -> Index Scan using name_speed on
> identity (cost=0.00..3.01 rows=1 width=59)
> 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.43 rows=1 width=50)
> 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..695.15 rows=1275 width=73)
> 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)
> Index Cond: (("outer".event_id)::text =
> (event.event_id)::text)
> Filter: (event_date_time > '20021126'::bpchar)
>
>
> --
> "You can't make a program without broken egos."
> --
> Jeremiah Jahn <jeremiah(at)cs(dot)earlham(dot)edu>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>