From: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Slow Query - Postgres 9.2 |
Date: | 2016-03-02 20:31:43 |
Message-ID: | CAE_gQfV454ByODkpH_vhqWdsLXXLdEBaHtK87zVmu=SbufCJ8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all...
I'm working on a Slow Query. It's faster now (It was 20sec before) but
still not good.
Can you have a look and see if you can find something?
Cheers
Query:
WITH jobs AS (
SELECT
job.id,
job.clientid,
CONCAT(customer.company, ' ', customer.name_first, ' ',
customer.name_last) AS "identity",
job.gps_lat,
job.gps_long
FROM public.ja_jobs AS job
JOIN public.ja_customers AS customer ON customer.id = job.customerid
WHERE
job.clientid = 22
AND job.time_job >= 1422702000
AND job.time_job <= 1456743540
AND NOT job.deleted
AND NOT job.templated), items AS (
SELECT
job.identity,
COALESCE(bill_item.unit_cost, billable.unit_cost, 0) *
bill_item.quantity AS cost,
COALESCE(bill_item.unit_price, billable.unit_price, 0) *
bill_item.quantity AS revenue,
bill_item.quantity AS quantity,
note.n_quote_status,
bill_item.for_invoicing
FROM
jobs AS job
JOIN
public.ja_notes AS note
ON
note.jobid = job.id
AND note.note_type IN ('time', 'part')
JOIN
dm.bill_items AS bill_item
ON
bill_item.bill_item_id = note.bill_item_id
AND bill_item.for_invoicing
LEFT JOIN
dm.billables AS billable
ON
billable.billable_id = note.billable_id
JOIN
public.ja_mobiusers AS user_creator
ON
user_creator.id = note.mobiuserid
AND (
user_creator.name_first ilike 'Alan'
OR user_creator.name_last ilike 'Alan'
))SELECT
item.identity,
SUM(CASE WHEN item.for_invoicing THEN item.revenue ELSE 0 END) AS revenue,
SUM(CASE WHEN item.for_invoicing THEN item.quantity ELSE 0 END) AS quantity,
SUM(CASE WHEN item.for_invoicing THEN item.cost ELSE 0 END) AS costFROM
items AS itemGROUP BY
item.identityORDER BY
revenue DESC,
item.identity ASC
Explain analyze link: http://explain.depesz.com/s/IIDj
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2016-03-02 20:50:43 | Re: RLS on catalog tables would be helpful |
Previous Message | Adrian Klaver | 2016-03-02 20:26:23 | Re: pg_upgrade 9.5.1: pg_upgrade_support missing |