SQL causing 100% CPU usage on stage (not seen on prod yet, stage has more data)

[aslakknutsen]

 SELECT count(*) over () as cnt2 , * FROM "work_items" 
    LEFT JOIN iterations "iter" ON "work_items"."fields" @> concat('{"system.iteration": "', iter.id, '"}')::jsonb 
    WHERE "work_items"."deleted_at" IS NULL 
    AND 
    (
        (
            (
                (
                    (
                        (
                            (
                                "work_items"."space_id" = 'b5948976-22ab-4221-91c5-d70674469fdd'
                            ) 
                            AND 
                            (
                                "work_items"."fields" @> '{"system.iteration" : "6bab05ad-56fd-4f96-bc12-bbfc8f4a1f65"}'
                            )
                        )
                        OR
                        (
                            "work_items"."fields" @> '{"system.labels" : ["6bab05ad-56fd-4f96-bc12-bbfc8f4a1f65"]}'
                        )
                    )
                    OR
                    (
                        "iter"."name" = 'x'
                    )
                )
                AND
                (
                    (
                        NOT 
                        (
                            "work_items"."fields" @> '{"system.state" : "closed"}'
                        )
                        AND NOT
                        (
                            "work_items"."fields" @> '{"system.state" : "Done"}'
                        )
                    )
                    AND NOT 
                    (
                        "work_items"."fields" @> '{"system.state" : "Removed"}'
                    )
                )
            )
        )
    )
    ORDER BY execution_order desc LIMIT 200 OFFSET 0;

This ends up in a full table scan of iterations and loop over each iteration against the workitems table etc. 57000*124000

This basically never returns.

If you add a

AND iter.space_id = $2 

it returns in 0.7ms.

[kwk]

@aslakknutsen

This ends up in a full table scan of iterations and loop over each iteration against the workitems table etc. 57000*124000

but the

LEFT JOIN iterations "iter" ON "work_items"."fields" @> concat('{"system.iteration": "', iter.id, '"}')::jsonb 

should ensure that only one area is joined to each work item, shouldn't it?

[GeorgeActon]

@kwk @aslakknutsen if this affects OSIO, can you create a bug on openshifio and link to this.
thats where we get visibility to our team

[kwk]

@kwk @aslakknutsen if this affects OSIO, can you create a bug on openshifio and link to this.
thats where we get visibility to our team

@GeorgeActon done: openshiftio/openshift.io#4124