Hello.
Thanks for the help.
Tom Lane wrote:
Jessi Berkelhammer <jberkelhammer@xxxxxxxx> writes:
Here are the 3 EXPLAIN ANALYZE commands followed by the output:
Well, here's the problem:
Join Filter: (clinical_reg_current.client_id = client.client_id)
-> Subquery Scan clinical_reg_current (cost=754.36..758.23
rows=1 width=4) (actual time=57.359..146.717 rows=684 loops=1)
Filter: (tier_program(benefit_type_code) = 'SAGE'::text)
-> Unique (cost=754.36..756.47 rows=117 width=211)
(actual time=56.427..67.998 rows=1000 loops=1)
For some reason it's estimating only one row out of the
clinical_reg_current view will satisfy the
tier_program(benefit_type_code) = 'SAGE' constraint. This causes it to
think a nestloop join to the client view would be a good idea. The same
estimation error is present in your example with the function and no
join, but it doesn't hurt anything because there are no planning
decisions that depend on the estimate in that case.
The estimate of the view's rowcount without the filter isn't that great
either (117 vs 1000 actual) but it's not wrong enough to prompt selection
of a bad plan choice. There's something funny going on with the
estimation of the function's selectivity --- does the expression
"tier_program(benefit_type_code)" match an index, perhaps? If so, have
you updated stats for that table lately?
Our database is analyzed & vacuumed nightly.
I'm also wondering why the function call isn't getting pushed down
further into the plan --- what's the definition of that view look like?
Here is the definition of the function:
Schema | Name | Result data type | Argument data types |
Owner | Language |
Source code | Description
--------+--------------+------------------+---------------------+---------+----------+-----------------------------------------------------------------------------------------------------------+-------------
public | tier_program | text | character varying |
Chasers | sql |
|
:
: SELECT COALESCE(clinical_project_code,description)
FROM l_benefit_type WHERE benefit_type_code = $1;
:
:
Here is the definition of clinical_reg_current view:
View "public.clinical_reg_current"
Column | Type |
Modifiers | Description
------------------------------+--------------------------------+-----------+-------------
clinical_reg_id | integer |
|
client_id | integer |
|
clinical_reg_date | date |
|
benefit_type_code | character varying(10) |
|
funding_source_code | character varying(10) |
|
clinical_reg_date_end | date |
|
clinical_exit_reason_code | character varying(10) |
|
kc_authorization_id | integer |
|
kc_authorization_status_code | character varying(10) |
|
current_case_rate | numeric(8,2) |
|
case_rate_reason_code | character varying(10) |
|
kc_exit_type_code | character varying(10) |
|
added_by | integer |
|
added_at | timestamp(0) without time zone |
|
changed_by | integer |
|
changed_at | timestamp(0) without time zone |
|
is_deleted | boolean |
|
deleted_at | timestamp(0) without time zone |
|
deleted_by | integer |
|
deleted_comment | text |
|
sys_log | text |
|
View definition:
SELECT DISTINCT ON (clinical_reg.client_id)
clinical_reg.clinical_reg_id, clinical_reg.client_id,
clinical_reg.clinical_reg_date, clinical_reg.benefit_type_code,
clinical_reg.funding_source_code, clinical_reg.clinical_reg_date_end,
clinical_reg.clinical_exit_reason_code,
clinical_reg.kc_authorization_id,
clinical_reg.kc_authorization_status_code,
clinical_reg.current_case_rate, clinical_reg.case_rate_reason_code,
clinical_reg.kc_exit_type_code, clinical_reg.added_by,
clinical_reg.added_at, clinical_reg.changed_by, clinical_reg.changed_at,
clinical_reg.is_deleted, clinical_reg.deleted_at,
clinical_reg.deleted_by, clinical_reg.deleted_comment, clinical_reg.sys_log
FROM clinical_reg
WHERE clinical_reg.clinical_reg_date <= 'now'::text::date AND
(clinical_reg.clinical_reg_date_end >= 'now'::text::date OR
clinical_reg.clinical_reg_date_end IS NULL) AND
(clinical_reg.kc_authorization_status_code::text <> ALL
(ARRAY['CX'::character varying, 'TM'::character varying]::text[])) AND
(clinical_reg.benefit_type_code::text <> ALL (ARRAY['75'::character
varying, '98'::character varying, '99'::character varying,
'00'::character varying]::text[]))
ORDER BY clinical_reg.client_id, clinical_reg.clinical_reg_date DESC;
The clinical_reg view is everything from tbl_clinical_reg, where
is_deleted is false.
tbl_clinical_reg, the underlying table has the same columns as
clinical_reg_current and clinical_reg. The other information from its
definition is here:
Indexes:
"tbl_clinical_reg_pkey" PRIMARY KEY, btree (clinical_reg_id)
"tbl_clinical_reg_kc_authorization_id_key" UNIQUE, btree
(kc_authorization_id)
"index_tbl_clinical_reg_benefit_type_code" btree
(benefit_type_code) WHERE NOT is_deleted
"index_tbl_clinical_reg_benefit_type_status" btree
(benefit_type_code, kc_authorization_status_code) WHERE NOT is_deleted
"index_tbl_clinical_reg_client_id" btree (client_id) WHERE NOT
is_deleted
"index_tbl_clinical_reg_client_id_clinical_reg_date" btree
(client_id, clinical_reg_date) WHERE NOT is_deleted
"index_tbl_clinical_reg_client_id_dates" btree (client_id,
clinical_reg_date, clinical_reg_date_end) WHERE NOT is_deleted
"index_tbl_clinical_reg_clinical_reg_date" btree
(clinical_reg_date) WHERE NOT is_deleted
"index_tbl_clinical_reg_clinical_reg_dates" btree
(clinical_reg_date, clinical_reg_date_end) WHERE NOT is_deleted
"index_tbl_clinical_reg_dates_client_id" btree
(clinical_reg_date_end, clinical_reg_date, client_id) WHERE NOT is_deleted
"index_tbl_clinical_reg_dates_client_id_deleted" btree
(clinical_reg_date_end, clinical_reg_date, client_id) WHERE NOT is_deleted
"index_tbl_clinical_reg_status_benefit_type" btree
(kc_authorization_status_code, benefit_type_code) WHERE NOT is_deleted
"index_tbl_clinical_reg_status_dates" btree
(kc_authorization_status_code, clinical_reg_date, clinical_reg_date_end)
WHERE NOT is_deleted
"index_tbl_clinical_reg_status_dates_client_id_deleted" btree
(kc_authorization_status_code, clinical_reg_date_end, clinical_reg_date,
client_id) WHERE NOT is_deleted
Check constraints:
"na_only_for_parke" CHECK (benefit_type_code::text <> 'NA'::text OR
funding_source_code::text = 'PARKE'::text)
"tbl_clinical_reg_check" CHECK (NOT is_deleted AND deleted_at IS
NULL OR is_deleted AND deleted_at IS NOT NULL)
"tbl_clinical_reg_check1" CHECK (NOT is_deleted AND deleted_by IS
NULL OR is_deleted AND deleted_by IS NOT NULL)
"tbl_clinical_reg_current_case_rate_check" CHECK (current_case_rate
>= 0::numeric)
Foreign-key constraints:
"tbl_clinical_reg_added_by_fkey" FOREIGN KEY (added_by) REFERENCES
tbl_staff(staff_id)
"tbl_clinical_reg_benefit_type_code_fkey" FOREIGN KEY
(benefit_type_code) REFERENCES l_benefit_type(benefit_type_code)
"tbl_clinical_reg_case_rate_reason_code_fkey" FOREIGN KEY
(case_rate_reason_code) REFERENCES
l_kc_case_rate_reason(kc_case_rate_reason_code)
"tbl_clinical_reg_changed_by_fkey" FOREIGN KEY (changed_by)
REFERENCES tbl_staff(staff_id)
"tbl_clinical_reg_client_id_fkey" FOREIGN KEY (client_id)
REFERENCES tbl_client(client_id)
"tbl_clinical_reg_clinical_exit_reason_code_fkey" FOREIGN KEY
(clinical_exit_reason_code) REFERENCES
l_clinical_exit_reason(clinical_exit_reason_code)
"tbl_clinical_reg_deleted_by_fkey" FOREIGN KEY (deleted_by)
REFERENCES tbl_staff(staff_id)
"tbl_clinical_reg_funding_source_code_fkey" FOREIGN KEY
(funding_source_code) REFERENCES l_funding_source(funding_source_code)
"tbl_clinical_reg_kc_authorization_status_code_fkey" FOREIGN KEY
(kc_authorization_status_code) REFERENCES
l_kc_authorization_status(kc_authorization_status_code)
"tbl_clinical_reg_kc_exit_type_code_fkey" FOREIGN KEY
(kc_exit_type_code) REFERENCES l_kc_exit_type(kc_exit_type_code)
Triggers:
tbl_clinical_reg_changed_at_update BEFORE UPDATE ON
tbl_clinical_reg FOR EACH ROW EXECUTE PROCEDURE auto_changed_at_update()
tbl_clinical_reg_log_chg AFTER INSERT OR DELETE OR UPDATE ON
tbl_clinical_reg FOR EACH ROW EXECUTE PROCEDURE table_log()
Has OIDs: no
Thank you.
take care,
jessi
--
Jessi Berkelhammer
Downtown Emergency Service Center
Computer Programming Specialist
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
