pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
cross table indexes or something?
14+ messages / 8 participants
[nested] [flat]

* cross table indexes or something?
@ 2003-11-26 20:14 Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:23 ` Re: cross table indexes or something? Marc A. Leith <marc@redboxdata.com>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  0 siblings, 2 replies; 14+ messages in thread

From: Jeremiah Jahn @ 2003-11-26 20:14 UTC (permalink / raw)
  To: pgsql-performance

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>




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
@ 2003-11-26 22:23 ` Marc A. Leith <marc@redboxdata.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Marc A. Leith @ 2003-11-26 22:23 UTC (permalink / raw)
  To: 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
> 




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
@ 2003-11-26 22:32 ` Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  1 sibling, 1 reply; 14+ messages in thread

From: Hannu Krosing @ 2003-11-26 22:32 UTC (permalink / raw)
  To: Jeremiah Jahn <jeremiah@cs.earlham.edu>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
@ 2003-12-01 14:29   ` Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 15:59     ` Re: cross table indexes or something? Richard Huxton <dev@archonet.com>
  2003-12-01 16:24     ` Re: cross table indexes or something? Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  0 siblings, 3 replies; 14+ messages in thread

From: Jeremiah Jahn @ 2003-12-01 14:29 UTC (permalink / raw)
  To: Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance

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_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=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=7 loops=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) (actual time=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) (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)
                     ->  Index Scan using event_actor_speed on event_actor  (cost=0.00..655.59 rows=1229 width=73) (actual time=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=0 loops=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>




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
@ 2003-12-01 15:59     ` Richard Huxton <dev@archonet.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Richard Huxton @ 2003-12-01 15:59 UTC (permalink / raw)
  To: Jeremiah Jahn <jeremiah@cs.earlham.edu>; Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
@ 2003-12-01 16:24     ` Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  2 siblings, 0 replies; 14+ messages in thread

From: Arjen van der Meijden @ 2003-12-01 16:24 UTC (permalink / raw)
  To: 'Jeremiah Jahn' <jeremiah@cs.earlham.edu>; 'Hannu Krosing' <hannu@tm.ee>; +Cc: pgsql-performance

> 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






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
@ 2003-12-01 19:47     ` Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2 siblings, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-12-01 19:47 UTC (permalink / raw)
  To: Jeremiah Jahn <jeremiah@cs.earlham.edu>; Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
@ 2003-12-02 19:08       ` Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-02 19:27         ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Jeremiah Jahn @ 2003-12-02 19:08 UTC (permalink / raw)
  To: pgsql-performance

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>




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
@ 2003-12-02 19:27         ` Josh Berkus <josh@agliodbs.com>
  2003-12-02 21:57           ` Re: cross table indexes or something? Neil Conway <neilc@samurai.com>
  2003-12-03 01:50           ` Re: cross table indexes or something? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 2 replies; 14+ messages in thread

From: Josh Berkus @ 2003-12-02 19:27 UTC (permalink / raw)
  To: Jeremiah Jahn <jeremiah@cs.earlham.edu>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-02 19:27         ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
@ 2003-12-02 21:57           ` Neil Conway <neilc@samurai.com>
  2003-12-02 23:04             ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Neil Conway @ 2003-12-02 21:57 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Jeremiah Jahn <jeremiah@cs.earlham.edu>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-02 19:27         ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 21:57           ` Re: cross table indexes or something? Neil Conway <neilc@samurai.com>
@ 2003-12-02 23:04             ` Josh Berkus <josh@agliodbs.com>
  2003-12-02 23:37               ` Re: cross table indexes or something? Neil Conway <neilc@samurai.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-12-02 23:04 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: Jeremiah Jahn <jeremiah@cs.earlham.edu>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-02 19:27         ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 21:57           ` Re: cross table indexes or something? Neil Conway <neilc@samurai.com>
  2003-12-02 23:04             ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
@ 2003-12-02 23:37               ` Neil Conway <neilc@samurai.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Neil Conway @ 2003-12-02 23:37 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: Jeremiah Jahn <jeremiah@cs.earlham.edu>; pgsql-performance

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

No, it was in 7.3

-Neil




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-02 19:27         ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
@ 2003-12-03 01:50           ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2003-12-03 02:04             ` Re: cross table indexes or something? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 1 reply; 14+ messages in thread

From: Christopher Kings-Lynne @ 2003-12-03 01:50 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Jeremiah Jahn <jeremiah@cs.earlham.edu>; pgsql-performance

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

Is this a TODO?

Chris





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: cross table indexes or something?
  2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-11-26 22:32 ` Re: cross table indexes or something? Hannu Krosing <hannu@tm.ee>
  2003-12-01 14:29   ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-01 19:47     ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-02 19:08       ` Re: cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
  2003-12-02 19:27         ` Re: cross table indexes or something? Josh Berkus <josh@agliodbs.com>
  2003-12-03 01:50           ` Re: cross table indexes or something? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-12-03 02:04             ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 14+ messages in thread

From: Christopher Kings-Lynne @ 2003-12-03 02:04 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Josh Berkus <josh@agliodbs.com>; Jeremiah Jahn <jeremiah@cs.earlham.edu>; pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2003-12-03 02:04 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-26 20:14 cross table indexes or something? Jeremiah Jahn <jeremiah@cs.earlham.edu>
2003-11-26 22:23 ` Marc A. Leith <marc@redboxdata.com>
2003-11-26 22:32 ` Hannu Krosing <hannu@tm.ee>
2003-12-01 14:29   ` Jeremiah Jahn <jeremiah@cs.earlham.edu>
2003-12-01 15:59     ` Richard Huxton <dev@archonet.com>
2003-12-01 16:24     ` Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2003-12-01 19:47     ` Josh Berkus <josh@agliodbs.com>
2003-12-02 19:08       ` Jeremiah Jahn <jeremiah@cs.earlham.edu>
2003-12-02 19:27         ` Josh Berkus <josh@agliodbs.com>
2003-12-02 21:57           ` Neil Conway <neilc@samurai.com>
2003-12-02 23:04             ` Josh Berkus <josh@agliodbs.com>
2003-12-02 23:37               ` Neil Conway <neilc@samurai.com>
2003-12-03 01:50           ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-03 02:04             ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

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