Skip site navigation (1) Skip section navigation (2)

performance issue - view and derived field

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: performance issue - view and derived field
Date: 2008-02-08 09:56:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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_username as valet_completed_by,
        pv.u_username as pex_valet_completed_by,
        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
    "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

Next:From: Shavonne Marietta WijesingheDate: 2008-02-08 11:15:53
Subject: Serial not nulla
Previous:From: Tiziano SlackDate: 2008-02-07 23:10:46
Subject: Re: TG_TABLE_NAME as identifier

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group