Re: oddly slow query

Hello.

Thanks for the help.

Tom Lane wrote:
> Jessi Berkelhammer <jberkelhammer(at)desc(dot)org> 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