Re: oddly slow query - Mailing list pgsql-general

Hello.

Thanks for the help.

Tom Lane wrote:
> Jessi Berkelhammer <jberkelhammer@desc.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