From: | Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Long running INSERT+SELECT query |
Date: | 2018-04-26 18:08:01 |
Message-ID: | f2b804fc-bd9a-fcf4-f3bd-cb1b129daab4@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Without the query we are flying blind, so suggestions will have a ?
Here is one such query:
INSERT INTO cmdb_sp_usage_history
(created_by, updated_by, created_on, updated_on, mod_count,
summary_on, quarter, product, used_from, "user",
keystrokes, minutes_in_use, times_started, avg_keystrokes,
max_keystrokes, spkg_operational)
SELECT
2, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1,
CURRENT_TIMESTAMP, quarter.id, "spv"."product",
"usage"."used_from", "usage"."user",
coalesce(sum("usage"."keystrokes"), 0),
coalesce(sum("usage"."minutes_in_use"), 0),
coalesce(sum("usage"."times_started"), 0),
coalesce(avg("usage"."keystrokes"), 0),
coalesce(max("usage"."keystrokes"), 0),
bool_or("cmdb_ci"."operational")
FROM
"cmdb_program_daily_usage" "usage"
LEFT OUTER JOIN
"cmdb_program_instance" "p" ON "p"."id" = "usage"."program_instance"
LEFT OUTER JOIN
"cmdb_ci_spkg" "s" ON "s"."id" = "p"."spkg"
LEFT OUTER JOIN
"cmdb_ci" "cmdb_ci" ON "s"."id" = "cmdb_ci"."id"
LEFT OUTER JOIN
"cmdb_software_product_version" "spv" ON "spv"."id" = "s"."software"
WHERE ("usage"."minutes_in_use" > 0)
AND ((NOT ("s"."software" IS NULL))
AND ((NOT ("s"."os" = TRUE))
OR ("s"."os" IS NULL)))
AND ("usage"."usage_date" >= quarter.start_date)
AND ("usage"."usage_date" < quarter.end_date)
GROUP BY "spv"."product", "usage"."used_from", "usage"."user"
HAVING (coalesce(sum("usage"."keystrokes"), 0) > 0) OR
(coalesce(sum("usage"."minutes_in_use"), 0) > 0) OR
(coalesce(sum("usage"."times_started"), 0) > 0)
ORDER BY "spv"."product", "usage"."used_from", "usage"."user";
Regards,
Vitaliy
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-04-26 18:46:10 | Re: Long running INSERT+SELECT query |
Previous Message | Adrian Klaver | 2018-04-26 17:51:25 | Re: Long running INSERT+SELECT query |