Slow Query - Postgres 9.2

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

Responses

Browse pgsql-general by date

  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