Re: Long running INSERT+SELECT query

[Vitaliy Garnashevich <vgarnashevich@xxxxxxxxx>]

> 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