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 cost
FROM
items AS item
GROUP BY
item.identity
ORDER BY
revenue DESC,
item.identity ASCExplain analyze link: http://explain.depesz.com/s/IIDj
