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

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@redboxdata.com

Quoting Jeremiah Jahn <jeremiah@cs.earlham.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@cs.earlham.edu>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>