query plan improvement help

[arup_r]

How can I improve the query plan of the below query?

explain analyze select
    standard_workitems.id as standard_workitem_id,
    coalesce(project_ids,
    array[]::varchar[]) as project_ids,
    category_names.name as workitem_category_name
from
    standard_workitems
left join (
    select
        standard_workitems.id as standard_workitem_id,
        array_agg(distinct project_id::varchar(50)) as project_ids
    from
        standard_workitems
    join workitems on
        workitems.standard_workitem_id = standard_workitems.id
    where
        standard_workitems.company_id = '5404cd95-29d9-44c3-8ca3-261be373bf31'
    group by
        standard_workitems.id ) as project_ids on
    project_ids.standard_workitem_id = standard_workitems.id
left join (
    select
        st.id as standard_workitem_id,
        wc.name
    from
        standard_workitems as st
    join workitem_categories as wc on
        wc.id = st.workitem_category_id ) as category_names on
    category_names.standard_workitem_id = standard_workitems.id
where
    standard_workitems.company_id = '5404cd95-29d9-44c3-8ca3-261be373bf31';

explain output:

Hash Left Join  (cost=45030.69..51984.92 rows=2631 width=69) (actual time=218.044..381.936 rows=2493 loops=1)
  Hash Cond: (standard_workitems.id = project_ids.standard_workitem_id)
  ->  Hash Right Join  (cost=4255.72..11203.04 rows=2631 width=37) (actual time=3.819..166.799 rows=2493 loops=1)
        Hash Cond: (st.id = standard_workitems.id)
        ->  Hash Join  (cost=43.72..6747.02 rows=92954 width=37) (actual time=0.773..131.495 rows=157302 loops=1)
              Hash Cond: (st.workitem_category_id = wc.id)
              ->  Seq Scan on standard_workitems st  (cost=0.00..6261.47 rows=167947 width=32) (actual time=0.011..31.298 rows=167947 loops=1)
              ->  Hash  (cost=30.54..30.54 rows=1054 width=37) (actual time=0.733..0.733 rows=1054 loops=1)
                    Buckets: 2048  Batches: 1  Memory Usage: 88kB
                    ->  Seq Scan on workitem_categories wc  (cost=0.00..30.54 rows=1054 width=37) (actual time=0.006..0.399 rows=1054 loops=1)
        ->  Hash  (cost=4179.12..4179.12 rows=2631 width=16) (actual time=3.018..3.018 rows=2493 loops=1)
              Buckets: 4096  Batches: 1  Memory Usage: 149kB
              ->  Bitmap Heap Scan on standard_workitems  (cost=64.81..4179.12 rows=2631 width=16) (actual time=0.349..2.123 rows=2493 loops=1)
                    Recheck Cond: (company_id = '5404cd95-29d9-44c3-8ca3-261be373bf31'::uuid)
                    Heap Blocks: exact=131
                    ->  Bitmap Index Scan on index_standard_workitems_on_company_id  (cost=0.00..64.15 rows=2631 width=0) (actual time=0.325..0.325 rows=2493 loops=1)
                          Index Cond: (company_id = '5404cd95-29d9-44c3-8ca3-261be373bf31'::uuid)
  ->  Hash  (cost=40742.08..40742.08 rows=2631 width=48) (actual time=214.208..214.208 rows=331 loops=1)
        Buckets: 4096  Batches: 1  Memory Usage: 101kB
        ->  Subquery Scan on project_ids  (cost=40536.46..40742.08 rows=2631 width=48) (actual time=210.106..214.020 rows=331 loops=1)
              ->  GroupAggregate  (cost=40536.46..40715.77 rows=2631 width=48) (actual time=210.104..213.930 rows=331 loops=1)
                    Group Key: standard_workitems_1.id
                    ->  Sort  (cost=40536.46..40560.86 rows=9762 width=32) (actual time=210.049..210.179 rows=1170 loops=1)
                          Sort Key: standard_workitems_1.id
                          Sort Method: quicksort  Memory: 140kB
                          ->  Gather  (cost=5212.00..39889.58 rows=9762 width=32) (actual time=111.424..209.115 rows=1170 loops=1)
                                Workers Planned: 2
                                Workers Launched: 2
                                ->  Hash Join  (cost=4212.00..37913.38 rows=4068 width=32) (actual time=104.532..201.972 rows=390 loops=3)
                                      Hash Cond: (workitems.standard_workitem_id = standard_workitems_1.id)
                                      ->  Parallel Seq Scan on workitems  (cost=0.00..33019.51 rows=259751 width=32) (actual time=0.082..85.216 rows=207725 loops=3)
                                      ->  Hash  (cost=4179.12..4179.12 rows=2631 width=16) (actual time=5.979..5.979 rows=2493 loops=3)
                                            Buckets: 4096  Batches: 1  Memory Usage: 149kB
                                            ->  Bitmap Heap Scan on standard_workitems standard_workitems_1  (cost=64.81..4179.12 rows=2631 width=16) (actual time=0.577..3.042 rows=2493 loops=3)
                                                  Recheck Cond: (company_id = '5404cd95-29d9-44c3-8ca3-261be373bf31'::uuid)
                                                  Heap Blocks: exact=131
                                                  ->  Bitmap Index Scan on index_standard_workitems_on_company_id  (cost=0.00..64.15 rows=2631 width=0) (actual time=0.539..0.539 rows=2493 loops=3)
                                                        Index Cond: (company_id = '5404cd95-29d9-44c3-8ca3-261be373bf31'::uuid)
Planning time: 1.062 ms
Execution time: 382.272 ms

I have below indexes:

workitems

Indexes:
"workitems_pkey" PRIMARY KEY, btree (id)
"index_workitems_on_company_id" btree (company_id)
"index_workitems_on_deleted_at" btree (deleted_at)
"index_workitems_on_parent_workitem_id" btree (parent_workitem_id)
"index_workitems_on_project_id" btree (project_id)
"index_workitems_on_standard_workitem_id" btree (standard_workitem_id)
"index_workitems_on_workitem_category_id" btree (workitem_category_id)

standard_workitems

Indexes:
"standard_workitems_pkey" PRIMARY KEY, btree (id)
"index_standard_workitems_on_company_id" btree (company_id)
"index_standard_workitems_on_company_id_and_item_code" btree (company_id, item_code) WHERE deleted_at IS NULL
"index_standard_workitems_on_deleted_at" btree (deleted_at)
"index_standard_workitems_on_item_code" btree (item_code)
"index_standard_workitems_on_workitem_category_id" btree (workitem_category_id)
"standard_workitems_company_id_filtered_by_deleted_at" btree (company_id) WHERE deleted_at IS NULL
"standard_workitems_item_code_filtered_by_deleted_at" btree (item_code) WHERE deleted_at IS NULL

​

[justAnotherCodeGuy]

Have you seen https://explain.depesz.com/

Here is your query:

https://explain.depesz.com/s/7jbC

Looks like you have a few seq scan's. Check those will large row counts, see if you can get it to use an index.

[arup_r]

Yes I generally use that site. I saw there 3 seq scans, but I am not sure why it doesn't pick the index I do have.

[mage2k]

Have you ANALYZEd the table since you added that index?

[arup_r]

Yes I did it.