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

Please don't top post here.

On Mon, Jun 7, 2021 at 3:50 PM Ayub Khan <ayub.hp@gmail.com> wrote:
>
> @Christophe: yes I am using RDS performance insights, however it might be more helpful if it could give more info
aboutthe slowness of the queries and what improvements could be done to the queries itself.
 
>
> 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_idis 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=118040loops=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.002rows=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=3032702loops=1)
 
> Planning Time: 2.222 ms
> Execution Time: 3009.387 ms

I'd say that your problem is that account_transactions.updated_by is
numeric (which seems like a terrible idea) while app_user.user_id is
not, so the index can't be used.  Some extensions could detect that,
but you won't be able to install them on RDS.