performance issue - view and derived field - Mailing list pgsql-sql

From Gary Stainburn
Subject performance issue - view and derived field
Date
Msg-id 200802080956.58270.gary.stainburn@ringways.co.uk
Whole thread Raw
List pgsql-sql
Hi folks.

I have a view used_diary_details shown below.

If I do a basic search on one of the fields of the underlying table the select 
takes under 1/2 second.
If I do a search using the derived field it takes over 15 seconds.

Anyone know how I can improve this as it's killing my app.

-- Takes 1/2 second
select count(ud_id) from used_diary_details     where ud_required >= CURRENT_DATE and     ud_required <=
CURRENT_DATE+7;

-- Takes 15 seconds
select count(ud_id) from used_diary_details    where valet_required >= CURRENT_DATE and     valet_required <=
CURRENT_DATE+7;


create  view used_diary_details as SELECT ud.*, ud.ud_required - CURRENT_DATE AS remaining,
work_date(ud.ud_required,-2)as ud_clean_date,       work_date(ud.ud_required,-2) - CURRENT_DATE AS ud_clean_days,
coalesce(uco.count,0)AS comments,        u.u_username, u.u_sales_mode AS dept, c.u_username AS completed_name,
v.u_usernameas valet_completed_by,       pv.u_username as pex_valet_completed_by,       tos_desc,        d.d_des as
dealership,      dd.dd_desc as department,       dd.dd_tos_required, dd.dd_default_tos,       tr.tr_desc as tax, not
coalesce(tr.tr_notax,false)as tax_required,       case when ud_valet_required is not null then ud_valet_required
   when ud_handover_date is not null and ud_required_time is null 
 
then work_date(ud_handover_date,-1)            when ud_handover_date is not null and ud_required_time < '10:01' 
then work_date(ud_handover_date,-1)            when ud_handover_date is not null and ud_required_time >= '10:01' 
then ud_handover_date            when ud_required_time is null then work_date(ud_required,-1)            when
ud_required_time< '10:01' then work_date(ud_required,-1)            else ud_required       end as valet_required,
dv_descas valet  FROM used_diary ud  JOIN users u ON ud.ud_u_id = u.u_id  LEFT JOIN users c ON ud.ud_completed_by =
c.u_id LEFT JOIN users v ON ud.ud_valet_completed_by = v.u_id  LEFT JOIN users pv ON ud.ud_pex_valet_completed_by =
pv.u_id left join type_of_sale t on t.tos_id = ud.ud_tos_id  join dealerships d on d.d_id = ud.ud_d_id  join
diary_departmentsdd on dd.dd_id = ud.ud_dd_id  left join tax_rfl_values tr on tr.tr_id = ud_tr_id  left join
diary_valet_optionsdv on dv.dv_id = ud_pex_valet_option  LEFT JOIN ( SELECT used_comments.uco_ud_id,
count(used_comments.uco_ud_id)
 
AS count  FROM used_comments GROUP BY used_comments.uco_ud_id) uco ON uco.uco_ud_id = ud.ud_id;
                                                  Table "public.used_diary"         Column           |            Type
---------------------------+-----------------------------ud_id                     | integerud_d_id                   |
integerud_registration          | character varying(12)ud_stock                  | character varying(7)ud_name
        | character varying(50)ud_required               | dateud_rfl                    | character
varying(25)ud_comments              | textud_created                | timestamp with time zoneud_completed
|timestamp with time zoneud_u_id                   | integerud_completed_by           | integerud_dd_id
| integerud_vin                    | character varying(20)ud_authorized             | timestamp without time
zoneud_authorized_by         | integerud_tab                    | integerud_tos_id                 | integerud_debt
             | numeric(7,2)ud_m_id                   | integerud_chit                   | character varying(12)ud_cc_id
                | character(2)ud_onsite_date            | dateud_onsite                 | booleanud_pl_id
  | character(1)ud_required_time          | character varying(5)ud_tr_id                  | integerud_pex_exists
    | booleanud_pex_registration       | character varying(12)ud_pex_make_model         | character
varying(40)ud_valet_instructions    | textud_valet_completed        | timestamp without time zoneud_valet_completed_by
  | integerud_pex_valet_completed    | timestamp without time zoneud_pex_valet_completed_by |
integerud_pex_valet_option      | integerud_pex_valet_instructions | textud_do_valet               |
booleanud_valet_required        | dateud_handover_date          | date
 
Indexes:   "used_diary_pkey" PRIMARY KEY, btree (ud_id)   "used_diary_completed_index" btree (ud_completed)
"used_diary_dealer_index"btree (ud_d_id)   "used_diary_dept_index" btree (ud_dd_id)   "used_diary_handover_date" btree
(ud_handover_date)  "used_diary_reg_index" btree (ud_registration)   "used_diary_required" btree (ud_required)
"used_diary_stock_index"btree (ud_stock)   "used_diary_ud_pex_valet_completed" btree (ud_pex_valet_completed)
"used_diary_ud_valet_completed"btree (ud_valet_completed)   "used_diary_valet_required" btree (ud_valet_required)
"used_diary_vin_index"btree (ud_vin)
 

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     


pgsql-sql by date:

Previous
From: Tiziano Slack
Date:
Subject: Re: TG_TABLE_NAME as identifier
Next
From: "Shavonne Marietta Wijesinghe"
Date:
Subject: Serial not nulla