Thread: cross table indexes or something?

cross table indexes or something?

From
Jeremiah Jahn
Date:
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_actorwhere 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_idand 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>


Re: cross table indexes or something?

From
"Marc A. Leith"
Date:
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
>


Re: cross table indexes or something?

From
Hannu Krosing
Date:
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.

Have you run ANALYZE ? Why does DB think that there is only one judge
with name like SMITH% ?

-------------
Hannu

P.S.
Always send EXPLAIN ANALYZE output if asking for advice on [PERFORM]

-------------
Hannu

Re: cross table indexes or something?

From
Jeremiah Jahn
Date:
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.


>
> -------------
> Hannu
>
> P.S.
> Always send EXPLAIN ANALYZE output if asking for advice on [PERFORM]
 EXPLAIN ANALYZE select distinct actor.actor_id,court.id,court.name,role_class_code,full_name from
actor,identity,court,event,event_actorwhere 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_idand event_actor.event_id = event.event_id and event_date_time > '20021126' order by full_name; 
                                                                              QUERY PLAN
                                                                                                              

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=686.42..686.44 rows=1 width=92) (actual time=111923.877..111923.877 rows=0 loops=1)
   ->  Sort  (cost=686.42..686.43 rows=1 width=92) (actual time=111923.873..111923.873 rows=0 loops=1)
         Sort Key: identity.full_name, actor.actor_id, court.id, court.name, actor.role_class_code
         ->  Nested Loop  (cost=8.45..686.41 rows=1 width=92) (actual time=111923.836..111923.836 rows=0 loops=1)
               ->  Nested Loop  (cost=8.45..680.57 rows=1 width=144) (actual time=109958.426..111157.822 rows=2449
loops=1)
                     ->  Hash Join  (cost=8.45..9.62 rows=1 width=117) (actual time=109945.754..109945.896 rows=6
loops=1)
                           Hash Cond: (("outer".id)::text = ("inner".court_ori)::text)
                           ->  Seq Scan on court  (cost=0.00..1.10 rows=10 width=34) (actual time=0.015..0.048 rows=10
loops=1)
                           ->  Hash  (cost=8.45..8.45 rows=1 width=109) (actual time=109940.161..109940.161 rows=0
loops=1)
                                 ->  Nested Loop  (cost=0.00..8.45 rows=1 width=109) (actual time=10.367..109940.079
rows=7loops=1) 
                                       Join Filter: (("outer".court_ori)::text = ("inner".court_ori)::text)
                                       ->  Index Scan using name_speed on identity  (cost=0.00..3.01 rows=1 width=59)
(actualtime=10.202..238.497 rows=22436 loops=1) 
                                             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.42 rows=1 width=50)
(actualtime=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)
                     ->  Index Scan using event_actor_speed on event_actor  (cost=0.00..655.59 rows=1229 width=73)
(actualtime=11.815..198.759 rows=408 loops=6) 
                           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) (actual time=0.308..0.308
rows=0loops=2449) 
                     Index Cond: (("outer".event_id)::text = (event.event_id)::text)
                     Filter: (event_date_time > '20021126'::bpchar)
 Total runtime: 111924.833 ms
(23 rows)



>
> -------------
> Hannu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Jeremiah Jahn <jeremiah@cs.earlham.edu>


Re: cross table indexes or something?

From
Richard Huxton
Date:
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

Re: cross table indexes or something?

From
"Arjen van der Meijden"
Date:
> Jeremiah Jahn wrote:
>
> > 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.
>

I think you're mistaking the command EXPLAIN ANALYZE for the command
ANALYZE.
Have you actually run the command ANALYZE or perhaps even better if you
haven't vacuumed before: VACUUM FULL ANALYZE

If you have no idea what vacuum is, check the manual. If you've already
run such a VACUUM/ANALYZE-command, then ignore this message :)

Best regards,

Arjen van der Meijden




Re: cross table indexes or something?

From
Josh Berkus
Date:
Jeremiah,

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

No, Hannu meant that you may need to run the following command:

ANALYZE actor;

... to update the database statistics on the actors table.   That is a
maintainence task that needs to be run periodically.

If that doesn't fix the bad plan, then the granularity of statistics on the
full_name column needs updating; I suggest:

ALTER TABLE actor ALTER COLUMN full_name SET STATISTICS 100;
ANALYZE actor;

And if it's still choosing  a slow nested loop, up the stats to 250.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: cross table indexes or something?

From
Jeremiah Jahn
Date:
Thanks to all, I had already run analyze.  But the STATISTICS setting
seems to have worked. I'm just not sure what it did..? Would anyone care
to explain.


On Mon, 2003-12-01 at 13:47, Josh Berkus wrote:
> Jeremiah,
>
> > 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.
>
> No, Hannu meant that you may need to run the following command:
>
> ANALYZE actor;
>
> ... to update the database statistics on the actors table.   That is a
> maintainence task that needs to be run periodically.
>
> If that doesn't fix the bad plan, then the granularity of statistics on the
> full_name column needs updating; I suggest:
>
> ALTER TABLE actor ALTER COLUMN full_name SET STATISTICS 100;
> ANALYZE actor;
>
> And if it's still choosing  a slow nested loop, up the stats to 250.
--
Jeremiah Jahn <jeremiah@cs.earlham.edu>


Re: cross table indexes or something?

From
Josh Berkus
Date:
Jeremiah,

> Thanks to all, I had already run analyze.  But the STATISTICS setting
> seems to have worked. I'm just not sure what it did..? Would anyone care
> to explain.

The STATISTICS setting improves the granularity of statistics kept by the
query planner on that column; increasing the granularity (i.e. more random
samples) can significantly improve things in cases where you have data whose
distribution is significantly skewed.    Certainly whenever you see the query
planner using a slow nestloop becuase of a bad row-return estimate, it is one
of the first things to try.

Its drawbacks are 4-fold:
1) to keep it working, you will probably need to run ANALZYE more often than
you have been;
2) these ANALYZEs will take longer, and have the annoying side effect of
flooring your CPU while they do;
3) You will have to be sure that your vacuum plan includes vacuuming the
pg_statistic table as the database superuser, as that table will be getting
updated more often.
4) Currently, pg_dump does *not* back up statistics settings.  So you will
need to save a script which does this in preparation for having to restore
your database.

Which is why the stats are set low by default.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: cross table indexes or something?

From
Neil Conway
Date:
Josh Berkus <josh@agliodbs.com> writes:
> 1) to keep it working, you will probably need to run ANALZYE more
>    often than you have been;

I'm not sure why this would be the case -- can you elaborate?

> 4) Currently, pg_dump does *not* back up statistics settings.

Yes, it does.

-Neil


Re: cross table indexes or something?

From
Josh Berkus
Date:
Neil,

> > 1) to keep it working, you will probably need to run ANALZYE more
> >    often than you have been;
>
> I'm not sure why this would be the case -- can you elaborate?

For the more granular stats to be useful, they have to be accurate; otherwise
you'll go back to a nestloop as soon as the query planner encounters a value
that it doens't think is in the table at all.

>
> > 4) Currently, pg_dump does *not* back up statistics settings.
>
> Yes, it does.

Oh, good.   Was this a 7.4 improvement?   I missed that in the changelogs ....

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: cross table indexes or something?

From
Neil Conway
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Oh, good.   Was this a 7.4 improvement?

No, it was in 7.3

-Neil


Re: cross table indexes or something?

From
Christopher Kings-Lynne
Date:
> 4) Currently, pg_dump does *not* back up statistics settings.

Is this a TODO?

Chris



Re: cross table indexes or something?

From
Christopher Kings-Lynne
Date:
>> 4) Currently, pg_dump does *not* back up statistics settings.
>
>
> Is this a TODO?

Oops - sorry I thought you meant 'pg_dump does not back up statistics'.
    Probably still should be a TODO :)

Chris