Improve Performance by Left Join and Group By Count - Postgresql

Question

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)
  ->  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
        ->  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
              ->  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)
                    ->  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)
                    ->  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
                          ->  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)
                                ->  Seq Scan on layout_feature  (cost=0.00..543.38 rows=29538 width=8) (actual time=0.002..1.716 rows=29538 loops=1)
                                ->  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
                                      ->  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
                                            ->  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

Yes, it is, I am looking for ~2ms. because I have only 350 in layout_pad_data, 10000, rows in 20000 in session ` layout_pad_data *--1 layout_feature *--1 session` — Mansoor
– Mansoor, Commented Nov 14, 2019 at 17:17
let me give u the exact numbers for all 3 tables. sessions = 11379 layout_feature = 29538 layout_pad_data = 660 — Mansoor
– Mansoor, Commented Nov 14, 2019 at 17:25
So how fast do you need that to be? 1ms? The main time is spent in the hash join - something that is CPU limited. You probably get faster execution times if you upgrade to a more recent version (e.g. 12) which improved on that — user1822
– user1822, Commented Nov 14, 2019 at 17:30

Laurenz Albe · Accepted Answer · 2019-11-15 07:35:44Z

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.

Stack Exchange Network

Improve Performance by Left Join and Group By Count - Postgresql

1 Answer 1

Your Answer

Hot Network Questions

Improve Performance by Left Join and Group By Count - Postgresql

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions