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_username as 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_desc as 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_departments dd 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_options dv 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 | integer
ud_d_id | integer
ud_registration | character varying(12)
ud_stock | character varying(7)
ud_name | character varying(50)
ud_required | date
ud_rfl | character varying(25)
ud_comments | text
ud_created | timestamp with time zone
ud_completed | timestamp with time zone
ud_u_id | integer
ud_completed_by | integer
ud_dd_id | integer
ud_vin | character varying(20)
ud_authorized | timestamp without time zone
ud_authorized_by | integer
ud_tab | integer
ud_tos_id | integer
ud_debt | numeric(7,2)
ud_m_id | integer
ud_chit | character varying(12)
ud_cc_id | character(2)
ud_onsite_date | date
ud_onsite | boolean
ud_pl_id | character(1)
ud_required_time | character varying(5)
ud_tr_id | integer
ud_pex_exists | boolean
ud_pex_registration | character varying(12)
ud_pex_make_model | character varying(40)
ud_valet_instructions | text
ud_valet_completed | timestamp without time zone
ud_valet_completed_by | integer
ud_pex_valet_completed | timestamp without time zone
ud_pex_valet_completed_by | integer
ud_pex_valet_option | integer
ud_pex_valet_instructions | text
ud_do_valet | boolean
ud_valet_required | date
ud_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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
