1

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
  • Is 9ms bad? What is your goal? – richyen Nov 14 '19 at 17:02
  • 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 Nov 14 '19 at 17:17
  • 1
    But you are joining 29538 rows from layout_feature – a_horse_with_no_name Nov 14 '19 at 17:19
  • let me give u the exact numbers for all 3 tables. sessions = 11379 layout_feature = 29538 layout_pad_data = 660 – Mansoor Nov 14 '19 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 – a_horse_with_no_name Nov 14 '19 at 17:30
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.