Re: dexter on AWS RDS auto tune queries - Mailing list pgsql-performance

I am using pgMusted to analyze a slow query and there the suggestion is to create an index on   app2.user_id, however app2.user_id is a primary key.

below is the query and its explain:

select * from (
  SELECT
    act.*,
    app1.user_name AS created_by_username,
    app2.user_name AS last_updated_by_username
  FROM
    account_transactions AS act LEFT OUTER JOIN app_user AS app1 ON app1.user_id = act.created_by
    LEFT OUTER JOIN app_user AS app2 ON app2.user_id = act.last_updated_by
  WHERE act.is_deleted = 'false' AND
        act.CREATION_DATE BETWEEN TO_DATE('06/06/2021', 'DD-MM-YYYY') AND TO_DATE('07-06-2021', 'DD-MM-YYYY')
  ORDER BY act.ID DESC
) as items order by id desc

Sort  (cost=488871.14..489914.69 rows=417420 width=270) (actual time=2965.815..2979.921 rows=118040 loops=1)
  Sort Key: act.id DESC
  Sort Method: quicksort  Memory: 57607kB
  ->  Merge Left Join  (cost=422961.21..449902.61 rows=417420 width=270) (actual time=2120.021..2884.484 rows=118040 loops=1)
        Merge Cond: (act.last_updated_by = ((app2.user_id)::numeric))
        ->  Sort  (cost=7293.98..7301.62 rows=3054 width=257) (actual time=464.243..481.292 rows=118040 loops=1)
              Sort Key: act.last_updated_by
              Sort Method: quicksort  Memory: 50899kB
              ->  Nested Loop Left Join  (cost=0.87..7117.21 rows=3054 width=257) (actual time=0.307..316.148 rows=118040 loops=1)
                    ->  Index Scan using creation_date on account_transactions act  (cost=0.44..192.55 rows=3054 width=244) (actual time=0.295..67.330 rows=118040 loops=1)
"                          Index Cond: ((creation_date >= to_date('06/06/2021'::text, 'DD-MM-YYYY'::text)) AND (creation_date <= to_date('07-06-2021'::text, 'DD-MM-YYYY'::text)))"
                          Filter: ((is_deleted)::text = 'false'::text)
                    ->  Index Scan using app_user_pk on app_user app1  (cost=0.43..2.27 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=118040)
                          Index Cond: (user_id = act.created_by)
        ->  Sort  (cost=415667.22..423248.65 rows=3032573 width=21) (actual time=1655.748..1876.596 rows=3079326 loops=1)
              Sort Key: ((app2.user_id)::numeric)
              Sort Method: quicksort  Memory: 335248kB
              ->  Seq Scan on app_user app2  (cost=0.00..89178.73 rows=3032573 width=21) (actual time=0.013..575.630 rows=3032702 loops=1)
Planning Time: 2.222 ms
Execution Time: 3009.387 ms