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
