Primeiramente um feliz 2016 à todos! Estou com esta Query com Trace time de *2,020,000ms.*
A ideia é diminuir ao máximo isso. O que vocês aconselham? Obrigado! *CREATE TABLE:* https://bitbucket.org/snippets/lpossamai/rKeAM *SLOW 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 *RESULTADO DO EXPLAIN ANALYZE:* Limit (cost=3740.26..3740.66 rows=10 width=628) (actual time=8566.850..8566.850 rows=0 loops=1) CTE cand_logs -> Limit (cost=3737.76..3740.26 rows=1000 width=1061) (actual time=8566.148..8566.327 rows=1000 loops=1) -> Sort (cost=3737.76..3741.11 rows=1342 width=1061) (actual time=8566.146..8566.238 rows=1000 loops=1) Sort Key: "ja_feedlog"."gtime" Sort Method: top-N heapsort Memory: 564kB -> Index Scan using "ix_feedlog_client_time_notif" on "ja_feedlog" (cost=0.01..3668.04 rows=1342 width=1061) (actual time=155.275..8560.325 rows=2863 loops=1) Index Cond: (("clientid" = 5484) AND ("gtime" > 1447778000) AND ("log_type" = ANY (' {104,56,103,55,130,90} '::integer[]))) -> CTE Scan on "cand_logs" "f" (cost=0.00..20.00 rows=500 width=628) (actual time=8566.850..8566.850 rows=0 loops=1) Filter: "do_include" Rows Removed by Filter: 1000 Total runtime: 8567.127 ms
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
