From: | "Nick Fankhauser" <nickf(at)ontko(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: An Analyze question |
Date: | 2002-04-22 16:05:20 |
Message-ID: | NEBBLAAHGLEEPCGOBHDGGEDMENAA.nickf@ontko.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-04-22 16:56:40 | Re: An Analyze question |
Previous Message | Tom Lane | 2002-04-22 15:34:27 | Re: An Analyze question |