Re: Long running INSERT+SELECT query

On 04/26/2018 11:08 AM, Vitaliy Garnashevich wrote:
>
>> 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";

Have not worked through all of the above, but a first draft suggestion:

Move the SELECT minus the aggregation functions into a sub-query that
uses FOR UPDATE. Then do the aggregation on the results of the sub-query.

>
> Regards,
> Vitaliy
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com