Re: cross table indexes or something?

On Monday 01 December 2003 14:29, Jeremiah Jahn wrote:
> 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.

It's guessing there's approximately 1. I don't think PG measures
cross-correlation of various columns cross-table.

If role_class_code on table actor? If so, try:

CREATE INDEX test_judge_idx ON actor (actor_id) WHERE role_class_code =
'Judge';

And then similar for the other class-codes (assuming you've not got too many
of them). Or even just an index on (actor_id,role_class_code).

If role_class_code is on a different table, can you say which one? The problem
is clearly this step:

> ->  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)

Thats 4.883 * 22436 loops = 109555 milliseconds.
--
  Richard Huxton
  Archonet Ltd