From: Jeremiah Jahn <jeremiah@cs.earlham.edu>
To: postgres performance <pgsql-performance@postgresql.org>
Subject: cross table indexes or something?
Date: Wed, 26 Nov 2003 14:14:11 -0600
Message-ID: <1069877651.22346.13.camel@bluejay.goodinassociates.com> (raw)
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>
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: jeremiah@cs.earlham.edu
Subject: Re: cross table indexes or something?
In-Reply-To: <1069877651.22346.13.camel@bluejay.goodinassociates.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