1

I've got a slow query here... could you please help me to optimize it?

Query:

SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer,
       sum(revenue) AS revenue,
       sum(i.quantity) AS quantity,
       sum(i.cost) AS cost
FROM
  ( SELECT account.id,
           job.customerid,
           job.title,
           job.gps_lat,
           job.gps_long,
           status.label AS status,
           status.status_type_id,
           job.status_label_id,
           client."position",
           bill_item.quantity,
           client.businesstype,
           account.id AS clientid,
           client.name_first AS customer_name_first,
           client.name_last AS customer_name_last,
           job.id AS jobid,
           note.mobiuserid,
           bill_item.for_invoicing AS invoice,
           COALESCE(bill_item.unit_price, billable.unit_price, 0) AS unit_price,
           note.n_quote_status,
           COALESCE(bill_item.unit_cost, billable.unit_cost, 0) AS unit_cost,
           job.time_job,
           "user".name_first,
           "user".name_last,
           role.id AS roleid,
           role.name AS role_name,
           billable.billable_id AS taskid,
           COALESCE(labs.tag, billable.code) AS task_name,
           note.time_start,
           client.company,
           job.refnum,
           (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.for_invoicing AS invoiceable,
           COALESCE(extract('epoch'
                            FROM bill.ts_creation AT TIME ZONE 'UTC'), bill_item.invoice_id, NULL) IS NOT NULL AS invoiced
   FROM ja_clients AS account
   JOIN ja_customers AS client ON client.clientid = account.id
   JOIN ja_jobs AS job ON client.id=job.customerid
   JOIN ja_notes AS note ON note.jobid = job.id
   JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id
   LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
   LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id
   OR invoice.invoice_id=bill_item.invoice_id
   LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id
   LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id
   JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid
   JOIN ja_status AS status ON status.id = job.status_label_id
   JOIN ja_role AS ROLE ON ROLE.id="user".user_type
   WHERE note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text,
                                            ('part'::CHARACTER VARYING)::text ])
     AND NOT job.templated
     AND NOT job.deleted
     AND job.clientid = 6239
     AND time_job >= 1438351200
     AND time_job <= 1448888340
     AND bill_item.for_invoicing = TRUE) AS i
LEFT JOIN
  (SELECT customerid,
          SUM(cost) AS cost,
          SUM(quantity) AS quantity
   FROM
     (SELECT account.id,
             job.customerid,
             job.title,
             job.gps_lat,
             job.gps_long,
             status.label AS status,
             status.status_type_id,
             job.status_label_id,
             client."position",
             bill_item.quantity,
             client.businesstype,
             account.id AS clientid,
             client.name_first AS customer_name_first,
             client.name_last AS customer_name_last,
             job.id AS jobid,
             note.mobiuserid,
             bill_item.for_invoicing AS invoice,
             COALESCE(bill_item.unit_price, billable.unit_price, 0) AS unit_price,
             note.n_quote_status,
             COALESCE(bill_item.unit_cost, billable.unit_cost, 0) AS unit_cost,
             job.time_job,
             "user".name_first,
             "user".name_last,
             ROLE.id AS roleid,
                  ROLE.name AS role_name,
                       billable.billable_id AS taskid,
                       COALESCE(labs.tag, billable.code) AS task_name,
                       note.time_start,
                       client.company,
                       job.refnum,
                       (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.for_invoicing AS invoiceable,
                       COALESCE(extract('epoch'
                                        FROM bill.ts_creation AT TIME ZONE 'UTC'), bill_item.invoice_id, NULL) IS NOT NULL AS invoiced
      FROM ja_clients AS account
      JOIN ja_customers AS client ON client.clientid = account.id
      JOIN ja_jobs AS job ON client.id=job.customerid
      JOIN ja_notes AS note ON note.jobid = job.id
      JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id
      LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
      LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id
      OR invoice.invoice_id=bill_item.invoice_id
      LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id
      LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id
      JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid
      JOIN ja_status AS status ON status.id = job.status_label_id
      JOIN ja_role AS ROLE ON ROLE.id="user".user_type
      WHERE note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text,
                                               ('part'::CHARACTER VARYING)::text ])
        AND NOT job.templated
        AND NOT job.deleted
        AND job.clientid = 6239
        AND time_job >= 1438351200
        AND time_job <= 1448888340
        AND n_quote_status = 0 ) AS note_detail_report_view
   WHERE 1=1
     AND clientid = 6239
     AND time_job >= 1438351200
     AND time_job <= 1448888340
     AND n_quote_status = 0
   GROUP BY customerid) AS a ON a.customerid = i.customerid
WHERE 1=1
  AND clientid = 6239
  AND time_job >= 1438351200
  AND time_job <= 1448888340
  AND invoice = TRUE
GROUP BY customer,
         a.cost,
         a.quantity
ORDER BY revenue DESC

EXPLAIN ANALYZE:

"Sort  (cost=13487.81..13487.82 rows=1 width=111) (actual time=4185.111..4185.195 rows=86 loops=1)"
"  Sort Key: ("sum"((COALESCE("bill_item"."unit_price", "billable"."unit_price", 0::numeric) * "bill_item"."quantity")))"
"  Sort Method: quicksort  Memory: 35kB"
"  ->  HashAggregate  (cost=13487.79..13487.80 rows=1 width=111) (actual time=4184.709..4184.831 rows=86 loops=1)"
"        ->  Nested Loop Left Join  (cost=11680.92..13487.77 rows=1 width=111) (actual time=4047.450..4183.073 rows=368 loops=1)"
"              Join Filter: ("job"."customerid" = "job"."customerid")"
"              Rows Removed by Join Filter: 31648"
"              ->  Nested Loop  (cost=4937.02..6743.84 rows=1 width=55) (actual time=1738.978..1796.765 rows=368 loops=1)"
"                    ->  Nested Loop  (cost=4937.02..6737.03 rows=1 width=59) (actual time=1738.948..1793.308 rows=368 loops=1)"
"                          ->  Nested Loop  (cost=4937.02..6729.73 rows=1 width=63) (actual time=1720.976..1771.591 rows=368 loops=1)"
"                                ->  Nested Loop Left Join  (cost=4937.02..6722.82 rows=1 width=67) (actual time=1716.031..1762.661 rows=368 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=4937.02..6715.46 rows=1 width=63) (actual time=1698.913..1741.672 rows=368 loops=1)"
"                                            ->  Nested Loop Left Join  (cost=4816.99..6587.49 rows=1 width=79) (actual time=1698.886..1736.628 rows=368 loops=1)"
"                                                  ->  Nested Loop  (cost=4816.99..6580.47 rows=1 width=79) (actual time=1698.874..1734.007 rows=368 loops=1)"
"                                                        ->  Nested Loop  (cost=4816.99..6572.91 rows=1 width=57) (actual time=1678.673..1709.004 rows=439 loops=1)"
"                                                              ->  Nested Loop  (cost=4816.99..6524.48 rows=1 width=41) (actual time=1577.352..1598.754 rows=1340 loops=1)"
"                                                                    ->  Hash Join  (cost=4816.99..6516.20 rows=1 width=49) (actual time=1577.093..1585.876 rows=1340 loops=1)"
"                                                                          Hash Cond: ("job"."customerid" = "client"."id")"
"                                                                          ->  Bitmap Heap Scan on "ja_jobs" "job"  (cost=4290.89..5988.86 rows=328 width=20) (actual time=393.757..399.024 rows=1340 loops=1)"
"                                                                                Recheck Cond: (("clientid" = 6239) AND ("time_job" >= 1438351200) AND ("time_job" <= 1448888340))"
"                                                                                Filter: ((NOT "templated") AND (NOT "deleted"))"
"                                                                                ->  BitmapAnd  (cost=4290.89..4290.89 rows=436 width=0) (actual time=393.508..393.508 rows=0 loops=1)"
"                                                                                      ->  Bitmap Index Scan on "ix_clientid_jobs"  (cost=0.00..257.85 rows=13475 width=0) (actual time=72.159..72.159 rows=14971 loops=1)"
"                                                                                            Index Cond: ("clientid" = 6239)"
"                                                                                      ->  Bitmap Index Scan on "ix_timejob_jobs"  (cost=0.00..4032.63 rows=152256 width=0) (actual time=315.935..315.935 rows=162192 loops=1)"
"                                                                                            Index Cond: (("time_job" >= 1438351200) AND ("time_job" <= 1448888340) AND ("time_job" >= 1438351200) AND ("time_job" <= 1448888340))"
"                                                                          ->  Hash  (cost=523.89..523.89 rows=177 width=37) (actual time=1183.303..1183.303 rows=607 loops=1)"
"                                                                                Buckets: 1024  Batches: 1  Memory Usage: 41kB"
"                                                                                ->  Index Scan using "ix_clientid_customers" on "ja_customers" "client"  (cost=0.00..523.89 rows=177 width=37) (actual time=47.532..1181.684 rows=607 loops=1)"
"                                                                                      Index Cond: ("clientid" = 6239)"
"                                                                    ->  Index Only Scan using "ja_clients_pkey" on "ja_clients" "account"  (cost=0.00..8.27 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1340)"
"                                                                          Index Cond: ("id" = 6239)"
"                                                                          Heap Fetches: 1340"
"                                                              ->  Index Scan using "ix_job_fk" on "ja_notes" "note"  (cost=0.00..48.42 rows=1 width=32) (actual time=0.078..0.079 rows=0 loops=1340)"
"                                                                    Index Cond: ("jobid" = "job"."id")"
"                                                                    Filter: (("note_type")::"text" = ANY ('{time,part}'::"text"[]))"
"                                                                    Rows Removed by Filter: 0"
"                                                        ->  Index Scan using "pk_bill_items" on "bill_items" "bill_item"  (cost=0.00..7.55 rows=1 width=38) (actual time=0.051..0.053 rows=1 loops=439)"
"                                                              Index Cond: ("bill_item_id" = "note"."bill_item_id")"
"                                                              Filter: ("for_invoicing" AND "for_invoicing")"
"                                                              Rows Removed by Filter: 0"
"                                                  ->  Index Scan using "pk_bills" on "bills" "bill"  (cost=0.00..7.00 rows=1 width=16) (actual time=0.002..0.003 rows=0 loops=368)"
"                                                        Index Cond: ("bill_id" = "bill_item"."bill_id")"
"                                            ->  Bitmap Heap Scan on "invoices" "invoice"  (cost=120.03..127.95 rows=2 width=8) (actual time=0.009..0.009 rows=0 loops=368)"
"                                                  Recheck Cond: (("invoice_id" = "bill"."invoice_id") OR ("invoice_id" = "bill_item"."invoice_id"))"
"                                                  ->  BitmapOr  (cost=120.03..120.03 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=368)"
"                                                        ->  Bitmap Index Scan on "pk_invoices"  (cost=0.00..3.59 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=368)"
"                                                              Index Cond: ("invoice_id" = "bill"."invoice_id")"
"                                                        ->  Bitmap Index Scan on "pk_invoices"  (cost=0.00..3.59 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=368)"
"                                                              Index Cond: ("invoice_id" = "bill_item"."invoice_id")"
"                                      ->  Index Scan using "pk_billables" on "billables" "billable"  (cost=0.00..7.35 rows=1 width=20) (actual time=0.051..0.052 rows=1 loops=368)"
"                                            Index Cond: ("billable_id" = "note"."billable_id")"
"                                ->  Index Scan using "ja_mobiusers_pkey" on "ja_mobiusers" "user"  (cost=0.00..6.91 rows=1 width=12) (actual time=0.017..0.019 rows=1 loops=368)"
"                                      Index Cond: ("id" = "note"."mobiuserid")"
"                          ->  Index Only Scan using "status_pk" on "ja_status" "status"  (cost=0.00..7.29 rows=1 width=4) (actual time=0.053..0.054 rows=1 loops=368)"
"                                Index Cond: ("id" = "job"."status_label_id")"
"                                Heap Fetches: 368"
"                    ->  Index Only Scan using "pk_role" on "ja_role" "role"  (cost=0.00..6.80 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=368)"
"                          Index Cond: ("id" = "user"."user_type")"
"                          Heap Fetches: 368"
"              ->  HashAggregate  (cost=6743.90..6743.91 rows=1 width=23) (actual time=6.274..6.385 rows=87 loops=368)"
"                    ->  Nested Loop  (cost=4937.02..6743.89 rows=1 width=23) (actual time=42.756..2306.019 rows=439 loops=1)"
"                          ->  Nested Loop  (cost=4937.02..6737.07 rows=1 width=27) (actual time=42.743..2301.193 rows=439 loops=1)"
"                                ->  Nested Loop  (cost=4937.02..6729.78 rows=1 width=31) (actual time=42.729..2277.495 rows=439 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=4937.02..6722.86 rows=1 width=35) (actual time=42.711..2239.382 rows=439 loops=1)"
"                                            ->  Nested Loop Left Join  (cost=4937.02..6715.49 rows=1 width=37) (actual time=42.696..1956.164 rows=439 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=4816.99..6587.52 rows=1 width=53) (actual time=42.674..1949.220 rows=439 loops=1)"
"                                                        ->  Nested Loop  (cost=4816.99..6580.51 rows=1 width=53) (actual time=42.667..1874.440 rows=439 loops=1)"
"                                                              ->  Nested Loop  (cost=4816.99..6572.94 rows=1 width=36) (actual time=42.651..1250.301 rows=439 loops=1)"
"                                                                    ->  Nested Loop  (cost=4816.99..6524.48 rows=1 width=20) (actual time=41.177..87.139 rows=1340 loops=1)"
"                                                                          ->  Hash Join  (cost=4816.99..6516.20 rows=1 width=28) (actual time=41.150..71.112 rows=1340 loops=1)"
"                                                                                Hash Cond: ("job"."customerid" = "client"."id")"
"                                                                                ->  Bitmap Heap Scan on "ja_jobs" "job"  (cost=4290.89..5988.86 rows=328 width=20) (actual time=38.622..64.334 rows=1340 loops=1)"
"                                                                                      Recheck Cond: (("clientid" = 6239) AND ("time_job" >= 1438351200) AND ("time_job" <= 1448888340))"
"                                                                                      Filter: ((NOT "templated") AND (NOT "deleted"))"
"                                                                                      ->  BitmapAnd  (cost=4290.89..4290.89 rows=436 width=0) (actual time=38.420..38.420 rows=0 loops=1)"
"                                                                                            ->  Bitmap Index Scan on "ix_clientid_jobs"  (cost=0.00..257.85 rows=13475 width=0) (actual time=2.953..2.953 rows=14971 loops=1)"
"                                                                                                  Index Cond: ("clientid" = 6239)"
"                                                                                            ->  Bitmap Index Scan on "ix_timejob_jobs"  (cost=0.00..4032.63 rows=152256 width=0) (actual time=31.121..31.121 rows=162192 loops=1)"
"                                                                                                  Index Cond: (("time_job" >= 1438351200) AND ("time_job" <= 1448888340) AND ("time_job" >= 1438351200) AND ("time_job" <= 1448888340))"
"                                                                                ->  Hash  (cost=523.89..523.89 rows=177 width=16) (actual time=2.494..2.494 rows=607 loops=1)"
"                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 29kB"
"                                                                                      ->  Index Scan using "ix_clientid_customers" on "ja_customers" "client"  (cost=0.00..523.89 rows=177 width=16) (actual time=0.015..1.455 rows=607 loops=1)"
"                                                                                            Index Cond: ("clientid" = 6239)"
"                                                                          ->  Index Only Scan using "ja_clients_pkey" on "ja_clients" "account"  (cost=0.00..8.27 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1340)"
"                                                                                Index Cond: ("id" = 6239)"
"                                                                                Heap Fetches: 1340"
"                                                                    ->  Index Scan using "ix_job_fk" on "ja_notes" "note"  (cost=0.00..48.45 rows=1 width=32) (actual time=0.620..0.864 rows=0 loops=1340)"
"                                                                          Index Cond: ("jobid" = "job"."id")"
"                                                                          Filter: (("n_quote_status" = 0) AND (("note_type")::"text" = ANY ('{time,part}'::"text"[])))"
"                                                                          Rows Removed by Filter: 0"
"                                                              ->  Index Scan using "pk_bill_items" on "bill_items" "bill_item"  (cost=0.00..7.55 rows=1 width=33) (actual time=1.411..1.416 rows=1 loops=439)"
"                                                                    Index Cond: ("bill_item_id" = "note"."bill_item_id")"
"                                                        ->  Index Scan using "pk_bills" on "bills" "bill"  (cost=0.00..7.00 rows=1 width=16) (actual time=0.165..0.165 rows=0 loops=439)"
"                                                              Index Cond: ("bill_id" = "bill_item"."bill_id")"
"                                                  ->  Bitmap Heap Scan on "invoices" "invoice"  (cost=120.03..127.95 rows=2 width=8) (actual time=0.009..0.009 rows=0 loops=439)"
"                                                        Recheck Cond: (("invoice_id" = "bill"."invoice_id") OR ("invoice_id" = "bill_item"."invoice_id"))"
"                                                        ->  BitmapOr  (cost=120.03..120.03 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=439)"
"                                                              ->  Bitmap Index Scan on "pk_invoices"  (cost=0.00..3.59 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=439)"
"                                                                    Index Cond: ("invoice_id" = "bill"."invoice_id")"
"                                                              ->  Bitmap Index Scan on "pk_invoices"  (cost=0.00..3.59 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=439)"
"                                                                    Index Cond: ("invoice_id" = "bill_item"."invoice_id")"
"                                            ->  Index Scan using "pk_billables" on "billables" "billable"  (cost=0.00..7.36 rows=1 width=14) (actual time=0.639..0.640 rows=1 loops=439)"
"                                                  Index Cond: ("billable_id" = "note"."billable_id")"
"                                      ->  Index Scan using "ja_mobiusers_pkey" on "ja_mobiusers" "user"  (cost=0.00..6.91 rows=1 width=12) (actual time=0.080..0.081 rows=1 loops=439)"
"                                            Index Cond: ("id" = "note"."mobiuserid")"
"                                ->  Index Only Scan using "status_pk" on "ja_status" "status"  (cost=0.00..7.29 rows=1 width=4) (actual time=0.047..0.049 rows=1 loops=439)"
"                                      Index Cond: ("id" = "job"."status_label_id")"
"                                      Heap Fetches: 439"
"                          ->  Index Only Scan using "pk_role" on "ja_role" "role"  (cost=0.00..6.80 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=439)"
"                                Index Cond: ("id" = "user"."user_type")"
"                                Heap Fetches: 439"
"Total runtime: 4187.608 ms"

Just an update about it:

The below query is good, the missing piece is that the quantity & cost selectors are interchangeable. Believe the i case is covered with joyleak's new implementation, I just need the a-case. i and a look very similar, except A with an additional filter: n_quote_status = 0

How can I re-create with those options?

0

1 Answer 1

2

Although I'm not certian if this will speed up your query directly, I find it's best to start with simplfying your query by removing any extra tables and columns from your query (and sub-queries) that are not relvant. For instance I don't see a purpose for adding the big LEFT JOIN subquery. I would suggest removing this (and the a.cost, a.quantity in the GROUP BY at the end), as it doesn't seem to be used in the final calculation.

Also, I doubt you've designed this or have any control over this, but who made the decision to have both a ja_clients table and a ja_customer table, that seems very unclear what the difference is there.


EDIT

I simplified your query a bit, there are likely still tables joined in that are not required:

SELECT
    CONCAT(client.company, ' ', client.name_first, ' ', client.name_last) AS customer,
    SUM(COALESCE(bill_item.unit_price, billable.unit_price, 0) * bill_item.quantity) AS revenue,
    SUM(bill_item.quantity) AS quantity,
    SUM(COALESCE(bill_item.unit_cost, billable.unit_cost, 0) * bill_item.quantity) AS cost
FROM
    ja_clients AS account
        JOIN ja_customers AS client ON client.clientid = account.id
        JOIN ja_jobs AS job ON client.id=job.customerid
        JOIN ja_notes AS note ON note.jobid = job.id
        JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id
        LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
        LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id
            OR invoice.invoice_id=bill_item.invoice_id
        LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id
        LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id
        JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid
        JOIN ja_status AS status ON status.id = job.status_label_id
        JOIN ja_role AS ROLE ON ROLE.id="user".user_type
WHERE
    note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text, ('part'::CHARACTER VARYING)::text ]) AND
    NOT job.templated AND
    NOT job.deleted AND
    job.clientid = 6239 AND
    time_job >= 1438351200 AND
    time_job <= 1448888340 AND
    bill_item.for_invoicing = TRUE
GROUP BY
    customer
ORDER BY
    revenue DESC
;
1
  • The above query is good, the missing piece is that the quantity & cost selectors are interchangeable. believe the i case is covered with your new implementation, we just need the a-case. i and a look very similar, except A with an additional filter: n_quote_status = 0
    – user83914
    Commented Feb 9, 2016 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.