Please don't top post here.
On Mon, Jun 7, 2021 at 3:50 PM Ayub Khan <ayub...@gmail.com> wrote:
>
> @Christophe: yes I am using RDS performance insights, however it might be
> more helpful if it could give more info about the 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_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
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.
