Re: An Analyze question - Mailing list pgsql-admin

> Could we see the queries?  (No, I do not remember your view definitions.)

Sure! I'll append the details below. (I was hoping we had correctly guessed
the cause & you wouldn't need details...)


> Offhand I would think that 7.2 is smart enough to deal with this

We're on 7.1.3. We're working to meet some tight deadlines, so the 7.2
upgrade is about 3 weeks off. I'm leery of going to 7.2 right away because
we make heavy use of timestamps & I've noted that quite a few folks have
tripped over timestamp issues in the upgrade process, so I figure we'll want
some breathing room when we upgrade.

-NF



The details:

actor_cases is a view:

create view actor_cases as
select
      actor_case_assignment.actor_id,
      case_data.case_id,
      case_data.case_public_id,
      case_data.court_id,
      case_data.case_filed_date,
      case_data.case_disposition_date,
      case_data.case_reopen_date,
      case_data.global_case_type_code,
      case_data.local_case_type_code,
      case_data.case_disp_local_code,
      case_data.case_disp_global_code,
      case_data.case_title,
      local_case_type.local_case_type_desc,
    local_case_type.global_case_type_desc
 from
    actor_case_assignment,
      case_data,
      local_case_type
where
      actor_case_assignment.case_id = case_data.case_id
  and case_data.court_id = local_case_type.court_id
  and case_data.local_case_type_code = local_case_type.local_case_type_code;


local_case_type is a trivial lookup table with about 500 rows, so it has no
indexes.

case_data has a unique index on case_id.
case_data.court_id only has about 10 distinct values, so there is no index.
There are about 200,000 records in case_data.


actor_case_assignment has indexes on:
(actor_id, case_id) -unique
(actor_id)
There are about 1,000,000 records in actor_case_assignment.

Here are the before & after explains again:

Before:

monroe=# explain select * from actor_cases where actor_id = '18105A7313 53';
NOTICE:  QUERY PLAN:

Merge Join  (cost=27748.94..27807.92 rows=145 width=192)
  ->  Sort  (cost=27713.16..27713.16 rows=3410 width=144)
        ->  Nested Loop  (cost=0.00..27372.75 rows=3410 width=144)
              ->  Index Scan using actor_case_assignment_both on
actor_case_assignment  (cost=0.00..11766.67 rows=3410 width=24)
              ->  Index Scan using case_data_case_id on case_data
(cost=0.00..4.56 rows=1 width=120)
  ->  Sort  (cost=35.78..35.78 rows=522 width=48)
        ->  Seq Scan on local_case_type  (cost=0.00..12.22 rows=522
width=48)



After:

develop=#  explain select * from actor_cases where actor_id = '18105A7313
53';
NOTICE:  QUERY PLAN:

Hash Join  (cost=27801.99..53031.15 rows=306 width=192)
  ->  Hash Join  (cost=27788.47..51957.43 rows=11377 width=144)
        ->  Seq Scan on case_data  (cost=0.00..6932.35 rows=226535
width=120)
        ->  Hash  (cost=27693.03..27693.03 rows=11377 width=24)
              ->  Seq Scan on actor_case_assignment  (cost=0.00..27693.03
rows=11377 width=24)
  ->  Hash  (cost=12.22..12.22 rows=522 width=48)
        ->  Seq Scan on local_case_type  (cost=0.00..12.22 rows=522
width=48)


And the difference between before & after is these update statements:

update case_data set case_id = '18105'||case_id;
update case_data set court_id = '18105'||court_id;
update actor_case_assignment set actor_case_assignment_id =
'18105'||actor_case_assignment_id;
update actor_case_assignment set actor_id = '18105'||actor_id;
update actor_case_assignment set case_id = '18105'||case_id;
update local_case_type set court_id = '18105'||court_id;

All of the "id" fields are varchar(50);

There were similar updates on all keys in the DB, but this is everything I
did on the involved tables. A vacuum analyze was run after the updates.