Improve Performance by Left Join and Group By Count - Postgresql I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN.

How do I improve the left join?

I am using the below index 

create index session_opportunity_index on public.session using btree(opportunity_id,session_id)


Below is my query

  EXPLAIN ANALYZE

  SELECT session.session_id AS session_id ,session.opportunity_id AS opportunity_id,
  session.last_modified_at AS last_modified_at,count(distinct layout_feature.layout_id) AS no_of_layouts ,
  count(distinct layout_pad_data.pad_id) AS n,
  session.tags AS tags  FROM public.session  
  LEFT JOIN public.layout_feature ON session.session_id = layout_feature.session_id  
  LEFT JOIN public.layout_pad_data ON layout_feature.layout_id = layout_pad_data.layout_id  
  WHERE session.opportunity_id=263945 GROUP BY session.session_id 

  LIMIT 600 OFFSET 3


Below is my Query Plan

QUERY PLAN
Limit  (cost=910.74..921.45 rows=298 width=64) (actual time=8.219..9.243 rows=298 loops=1)
  -&gt;  GroupAggregate  (cost=910.63..921.45 rows=301 width=64) (actual time=8.206..9.226 rows=301 loops=1)
        Group Key: session.session_id
        -&gt;  Sort  (cost=910.63..912.58 rows=781 width=64) (actual time=8.184..8.214 rows=301 loops=1)
              Sort Key: session.session_id
              Sort Method: quicksort  Memory: 39kB
              -&gt;  Hash Right Join  (cost=831.86..873.11 rows=781 width=64) (actual time=8.070..8.099 rows=301 loops=1)
                    Hash Cond: (layout_pad_data.layout_id = layout_feature.layout_id)
                    -&gt;  Seq Scan on layout_pad_data  (cost=0.00..38.60 rows=660 width=8) (actual time=0.002..0.058 rows=660 loops=1)
                    -&gt;  Hash  (cost=822.10..822.10 rows=781 width=60) (actual time=7.689..7.689 rows=301 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 23kB
                          -&gt;  Hash Right Join  (cost=160.14..822.10 rows=781 width=60) (actual time=7.602..7.641 rows=301 loops=1)
                                Hash Cond: (layout_feature.session_id = session.session_id)
                                -&gt;  Seq Scan on layout_feature  (cost=0.00..543.38 rows=29538 width=8) (actual time=0.002..1.716 rows=29538 loops=1)
                                -&gt;  Hash  (cost=156.38..156.38 rows=301 width=56) (actual time=0.112..0.112 rows=301 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                      -&gt;  Bitmap Heap Scan on session  (cost=10.62..156.38 rows=301 width=56) (actual time=0.028..0.070 rows=301 loops=1)
                                            Recheck Cond: (opportunity_id = 123)
                                            Heap Blocks: exact=4
                                            -&gt;  Bitmap Index Scan on session_opportunity_index  (cost=0.00..10.54 rows=301 width=0) (actual time=0.024..0.024 rows=301 loops=1)
                                                  Index Cond: (opportunity_id = 123)
Planning time: 0.318 ms
Execution time: 9.351 ms
Your only hope is to get an execution plan that uses nested loop joins so that you don't have to read the whole layout_feature table.

CREATE INDEX ON layout_feature (session_id, layout_id);
VACUUM layout_feature;


The VACUUM is there to give you an index only scan. You have to keep the table well vacuumed for that to work.

Next, you should increase work_mem to get a hash aggregate rather than a sort + group aggregate (if that is possible here).

If one of the tables does not change often, or you don't need exact results, using materialized views can do a lot.