pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc A. Leith <marc@redboxdata.com>
To: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: cross table indexes or something?
Date: Wed, 26 Nov 2003 17:23:14 -0500
Message-ID: <1069885394.3fc527d23560e@webmail.nuvergence.com> (raw)
In-Reply-To: <1069877651.22346.13.camel@bluejay.goodinassociates.com>
References: <1069877651.22346.13.camel@bluejay.goodinassociates.com>

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
> 




view thread (14+ messages)  latest in thread

Message-ID: <1069885394.3fc527d23560e@webmail.nuvergence.com>
Permalink:  ../1069885394.3fc527d23560e@webmail.nuvergence.com/
Also on:    postgresql.org/message-id/1069885394.3fc527d23560e@webmail.nuvergence.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: marc@redboxdata.com
  Subject: Re: cross table indexes or something?
  In-Reply-To: <1069885394.3fc527d23560e@webmail.nuvergence.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox