Start page: &#34;slow&#34; response time #9502

@somera commented on GitHub (Sep 2, 2022):

                                          Tabelle »public.action«
    Spalte    |          Typ           | Sortierfolge | NULL erlaubt? |            Vorgabewert
--------------+------------------------+--------------+---------------+------------------------------------
 id           | bigint                 |              | not null      | nextval('action_id_seq'::regclass)
 user_id      | bigint                 |              |               |
 op_type      | integer                |              |               |
 act_user_id  | bigint                 |              |               |
 repo_id      | bigint                 |              |               |
 comment_id   | bigint                 |              |               |
 is_deleted   | boolean                |              | not null      | false
 ref_name     | character varying(255) |              |               |
 is_private   | boolean                |              | not null      | false
 content      | text                   |              |               |
 created_unix | bigint                 |              |               |
Indexe:
    "action_pkey" PRIMARY KEY, btree (id)
    "IDX_action_au_r_c_u_d" btree (act_user_id, repo_id, created_unix, user_id, is_deleted)
    "IDX_action_comment_id" btree (comment_id)
    "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted)

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

Ah... I've just seen that there are some "legacy" indices in my example above. I've just got the same schema as you and found the following query plan:

 tc5=# explain select action.* from action inner join "repository" ON "repository".id = "action".repo_id WHERE user_id=18059 AND is_deleted=false ORDER BY "action"."created_unix" DESC LIMIT 20
;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Limit  (cost=1677.66..1677.67 rows=5 width=393)
   ->  Sort  (cost=1677.66..1677.67 rows=5 width=393)
         Sort Key: action.created_unix DESC
         ->  Nested Loop  (cost=0.57..1677.60 rows=5 width=393)
               ->  Index Scan using "IDX_action_r_u_d" on action  (cost=0.29..1636.10 rows=5 width=393)
                     Index Cond: ((user_id = 18059) AND (is_deleted = false))
               ->  Index Only Scan using repository_pkey on repository  (cost=0.28..8.30 rows=1 width=8)
                     Index Cond: (id = action.repo_id)
(8 rows)

EDIT: I'd put up the wrong plan this is the right one

@zeripath commented on GitHub (Sep 2, 2022): Ah... I've just seen that there are some "legacy" indices in my example above. I've just got the same schema as you and found the following query plan: ``` tc5=# explain select action.* from action inner join "repository" ON "repository".id = "action".repo_id WHERE user_id=18059 AND is_deleted=false ORDER BY "action"."created_unix" DESC LIMIT 20 ; QUERY PLAN --------------------------------------------------------------------------------------------------------- Limit (cost=1677.66..1677.67 rows=5 width=393) -> Sort (cost=1677.66..1677.67 rows=5 width=393) Sort Key: action.created_unix DESC -> Nested Loop (cost=0.57..1677.60 rows=5 width=393) -> Index Scan using "IDX_action_r_u_d" on action (cost=0.29..1636.10 rows=5 width=393) Index Cond: ((user_id = 18059) AND (is_deleted = false)) -> Index Only Scan using repository_pkey on repository (cost=0.28..8.30 rows=1 width=8) Index Cond: (id = action.repo_id) (8 rows) ``` EDIT: I'd put up the wrong plan this is the right one

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

Damn I suspect this means that postgres needs different indices to that of MySQL and other DBs and I was led down the garden path by my previous testing with old indices present.

OK I guess we just need to twiddle with the indices until we find the quickest for this.

@zeripath commented on GitHub (Sep 2, 2022): Damn I suspect this means that postgres needs different indices to that of MySQL and other DBs and I was led down the garden path by my previous testing with old indices present. OK I guess we just need to twiddle with the indices until we find the quickest for this.

GiteaMirror commented

2025-11-02 08:40:55 -06:00

@somera commented on GitHub (Sep 2, 2022):

Damn I suspect this means that postgres needs different indices to that of MySQL and other DBs and I was led down the garden path by my previous testing with old indices present.

OK I guess we just need to twiddle with the indices until we find the quickest for this.

Ok. Sounds good.

@somera commented on GitHub (Sep 2, 2022): > Damn I suspect this means that postgres needs different indices to that of MySQL and other DBs and I was led down the garden path by my previous testing with old indices present. > > OK I guess we just need to twiddle with the indices until we find the quickest for this. Ok. Sounds good.

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

Could you compare which is faster between doing:

CREATE INDEX ON action (user_id, is_deleted)

And:

CREATE INDEX ON action (user_id)

?

@zeripath commented on GitHub (Sep 2, 2022): Could you compare which is faster between doing: ``` CREATE INDEX ON action (user_id, is_deleted) ``` And: ``` CREATE INDEX ON action (user_id) ``` ?

GiteaMirror commented

2025-11-02 08:40:55 -06:00

@somera commented on GitHub (Sep 2, 2022):

Not faster.

https://explain.depesz.com/s/6vv0

@somera commented on GitHub (Sep 2, 2022): Not faster. https://explain.depesz.com/s/6vv0

GiteaMirror commented

2025-11-02 08:40:55 -06:00

@zeripath commented on GitHub (Sep 2, 2022):

That's very weird because it should be using the index - which your explain does not show. Certainly it works for me.

You will likely need to drop those new indexes before restarting Gitea btw - (I've just discovered that there is a bug in xorm related to its schema reading.)

@zeripath commented on GitHub (Sep 2, 2022): That's very weird because it should be using the index - which your explain does not show. Certainly it works for me. You will likely need to drop those new indexes before restarting Gitea btw - (I've just discovered that there is a bug in xorm related to its schema reading.)

GiteaMirror commented

2025-11-02 08:40:56 -06:00

@zeripath commented on GitHub (Sep 2, 2022):

What version of postgres are you running?

@zeripath commented on GitHub (Sep 2, 2022): What version of postgres are you running?

GiteaMirror commented

2025-11-02 08:40:56 -06:00

@somera commented on GitHub (Sep 2, 2022):

Added CREATE INDEX ON action (user_id, is_deleted) -> https://explain.depesz.com/s/bqTF

Added CREATE INDEX ON action (user_id) -> https://explain.depesz.com/s/tJisn

Removed CREATE INDEX ON action (user_id, is_deleted) -> https://explain.depesz.com/s/oVKH

@somera commented on GitHub (Sep 2, 2022): Added CREATE INDEX ON action (user_id, is_deleted) -> https://explain.depesz.com/s/bqTF Added CREATE INDEX ON action (user_id) -> https://explain.depesz.com/s/tJisn Removed CREATE INDEX ON action (user_id, is_deleted) -> https://explain.depesz.com/s/oVKH

GiteaMirror commented

2025-11-02 08:40:56 -06:00

@somera commented on GitHub (Sep 2, 2022):

I'm using this

PostgreSQL 13.8 (Ubuntu 13.8-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

@zeripath here my PG Version.

@somera commented on GitHub (Sep 2, 2022): > I'm using this > > `PostgreSQL 13.8 (Ubuntu 13.8-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit` @zeripath here my PG Version.

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

I just don't understand why it isn't using any of the indices.

I mean this is the plan I get with the user_id and is_deleted index:

;
                                                   QUERY PLAN                                           >
-------------------------------------------------------------------------------------------------------->
 Limit  (cost=61.00..61.02 rows=5 width=393)
   ->  Sort  (cost=61.00..61.02 rows=5 width=393)
         Sort Key: action.created_unix DESC
         ->  Nested Loop  (cost=0.57..60.94 rows=5 width=393)
               ->  Index Scan using action_user_id_is_deleted_idx on action  (cost=0.29..19.44 rows=5 wi>
                     Index Cond: ((user_id = 18059) AND (is_deleted = false))
               ->  Index Only Scan using repository_pkey on repository  (cost=0.28..8.30 rows=1 width=8)
                     Index Cond: (id = action.repo_id)
(8 rows)

It just makes no sense to me that it would not use the index. That's why we have an index. Even the "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted) should have been enough.

@zeripath commented on GitHub (Sep 2, 2022): I just don't understand why it isn't using any of the indices. I mean this is the plan I get with the user_id and is_deleted index: ``` ; QUERY PLAN > --------------------------------------------------------------------------------------------------------> Limit (cost=61.00..61.02 rows=5 width=393) -> Sort (cost=61.00..61.02 rows=5 width=393) Sort Key: action.created_unix DESC -> Nested Loop (cost=0.57..60.94 rows=5 width=393) -> Index Scan using action_user_id_is_deleted_idx on action (cost=0.29..19.44 rows=5 wi> Index Cond: ((user_id = 18059) AND (is_deleted = false)) -> Index Only Scan using repository_pkey on repository (cost=0.28..8.30 rows=1 width=8) Index Cond: (id = action.repo_id) (8 rows) ``` It just makes no sense to me that it would not use the index. That's why we have an index. Even the ` "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted)` should have been enough.

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

I mean you could try:

CREATE INDEX ON action (created_unix DESC)

However, I note that even your no ORDER BY query still isn't using an index.

@zeripath commented on GitHub (Sep 2, 2022): I mean you could try: ``` CREATE INDEX ON action (created_unix DESC) ``` --- However, I note that even your no `ORDER BY` query still isn't using an index.

GiteaMirror commented

@somera commented on GitHub (Sep 2, 2022):

I just don't understand why it isn't using any of the indices.

I mean this is the plan I get with the user_id and is_deleted index:

;
                                                   QUERY PLAN                                           >
-------------------------------------------------------------------------------------------------------->
 Limit  (cost=61.00..61.02 rows=5 width=393)
   ->  Sort  (cost=61.00..61.02 rows=5 width=393)
         Sort Key: action.created_unix DESC
         ->  Nested Loop  (cost=0.57..60.94 rows=5 width=393)
               ->  Index Scan using action_user_id_is_deleted_idx on action  (cost=0.29..19.44 rows=5 wi>
                     Index Cond: ((user_id = 18059) AND (is_deleted = false))
               ->  Index Only Scan using repository_pkey on repository  (cost=0.28..8.30 rows=1 width=8)
                     Index Cond: (id = action.repo_id)
(8 rows)

It just makes no sense to me that it would not use the index. That's why we have an index. Even the "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted) should have been enough.

The problem is order by ...

Here the plan without this: https://explain.depesz.com/s/ygq3

@somera commented on GitHub (Sep 2, 2022): > I just don't understand why it isn't using any of the indices. > > I mean this is the plan I get with the user_id and is_deleted index: > > ``` > ; > QUERY PLAN > > --------------------------------------------------------------------------------------------------------> > Limit (cost=61.00..61.02 rows=5 width=393) > -> Sort (cost=61.00..61.02 rows=5 width=393) > Sort Key: action.created_unix DESC > -> Nested Loop (cost=0.57..60.94 rows=5 width=393) > -> Index Scan using action_user_id_is_deleted_idx on action (cost=0.29..19.44 rows=5 wi> > Index Cond: ((user_id = 18059) AND (is_deleted = false)) > -> Index Only Scan using repository_pkey on repository (cost=0.28..8.30 rows=1 width=8) > Index Cond: (id = action.repo_id) > (8 rows) > ``` > > It just makes no sense to me that it would not use the index. That's why we have an index. Even the ` "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted)` should have been enough. The problem is order by ... Here the plan without this: https://explain.depesz.com/s/ygq3

GiteaMirror commented

https://explain.depesz.com/s/K6u3

@somera commented on GitHub (Sep 2, 2022):

giteadb=# \d action
                                          Tabelle »public.action«
    Spalte    |          Typ           | Sortierfolge | NULL erlaubt? |            Vorgabewert
--------------+------------------------+--------------+---------------+------------------------------------
 id           | bigint                 |              | not null      | nextval('action_id_seq'::regclass)
 user_id      | bigint                 |              |               |
 op_type      | integer                |              |               |
 act_user_id  | bigint                 |              |               |
 repo_id      | bigint                 |              |               |
 comment_id   | bigint                 |              |               |
 is_deleted   | boolean                |              | not null      | false
 ref_name     | character varying(255) |              |               |
 is_private   | boolean                |              | not null      | false
 content      | text                   |              |               |
 created_unix | bigint                 |              |               |
Indexe:
    "action_pkey" PRIMARY KEY, btree (id)
    "IDX_action_au_r_c_u_d" btree (act_user_id, repo_id, created_unix, user_id, is_deleted)
    "IDX_action_comment_id" btree (comment_id)
    "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted)
    "action_created_unix_idx" btree (created_unix DESC)
    "action_user_id_idx" btree (user_id)

Looks better now (~600ms faster for the start page)

GiteaMirror commented

2025-11-02 08:40:58 -06:00

@somera commented on GitHub (Sep 2, 2022):

I remved

"action_user_id_idx" btree (user_id)

now.

@somera commented on GitHub (Sep 2, 2022): I remved `"action_user_id_idx" btree (user_id)` now.

GiteaMirror commented

2025-11-02 08:40:58 -06:00

@zeripath commented on GitHub (Sep 2, 2022):

What the hell is going on:

"Limit  (cost=0.71..19.21 rows=20 width=456) (actual time=0.088..0.371 rows=20 loops=1)"
"  ->  Nested Loop  (cost=0.71..604060.66 rows=653278 width=456) (actual time=0.085..0.358 rows=20 loops=1)"
"        ->  Index Scan using action_created_unix_idx on action  (cost=0.43..404005.15 rows=653278 width=456) (actual time=0.053..0.120 rows=20 loops=1)"
"              Filter: ((NOT is_deleted) AND (user_id = 1))"
"              Rows Removed by Filter: 20"
"        ->  Index Only Scan using repository_pkey on repository  (cost=0.29..0.31 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=20)"
"              Index Cond: (id = action.repo_id)"
"              Heap Fetches: 20"
"Planning Time: 1.418 ms"
"Execution Time: 0.457 ms"

Why would your db choose to use the index action_created_unix_idx!!

@zeripath commented on GitHub (Sep 2, 2022): What the hell is going on: ``` "Limit (cost=0.71..19.21 rows=20 width=456) (actual time=0.088..0.371 rows=20 loops=1)" " -> Nested Loop (cost=0.71..604060.66 rows=653278 width=456) (actual time=0.085..0.358 rows=20 loops=1)" " -> Index Scan using action_created_unix_idx on action (cost=0.43..404005.15 rows=653278 width=456) (actual time=0.053..0.120 rows=20 loops=1)" " Filter: ((NOT is_deleted) AND (user_id = 1))" " Rows Removed by Filter: 20" " -> Index Only Scan using repository_pkey on repository (cost=0.29..0.31 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=20)" " Index Cond: (id = action.repo_id)" " Heap Fetches: 20" "Planning Time: 1.418 ms" "Execution Time: 0.457 ms" ``` Why would your db choose to use the index `action_created_unix_idx`!!

GiteaMirror commented

2025-11-02 08:40:59 -06:00

@somera commented on GitHub (Sep 2, 2022):

I don't know. ;)

My action table has 1.307.514 entries.

@somera commented on GitHub (Sep 2, 2022): I don't know. ;) My action table has 1.307.514 entries.

GiteaMirror commented

2025-11-02 08:40:59 -06:00

@zeripath commented on GitHub (Sep 2, 2022):

Maybe you'd benefit from:

CREATE INDEX IDX_action_c_u_d ON action (created_unix DESC, user_id, is_deleted)

Possibly even append an INCLUDE (repo_id) there.

@zeripath commented on GitHub (Sep 2, 2022): Maybe you'd benefit from: ``` CREATE INDEX IDX_action_c_u_d ON action (created_unix DESC, user_id, is_deleted) ``` Possibly even append an `INCLUDE (repo_id)` there.

GiteaMirror commented

https://explain.depesz.com/s/YJuyr

@somera commented on GitHub (Sep 2, 2022):

Maybe you'd benefit from:
CREATE INDEX IDX_action_c_u_d ON action (created_unix DESC, user_id, is_deleted)
Possibly even append an INCLUDE (repo_id) there.

Indexe:
    "action_pkey" PRIMARY KEY, btree (id)
    "IDX_action_au_r_c_u_d" btree (act_user_id, repo_id, created_unix, user_id, is_deleted)
    "IDX_action_comment_id" btree (comment_id)
    "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted)
    "idx_action_c_u_d" btree (created_unix DESC, user_id, is_deleted)

The start page feels better.

@somera commented on GitHub (Sep 2, 2022): > Maybe you'd benefit from: > > ``` > CREATE INDEX IDX_action_c_u_d ON action (created_unix DESC, user_id, is_deleted) > ``` > > Possibly even append an `INCLUDE (repo_id)` there. ``` Indexe: "action_pkey" PRIMARY KEY, btree (id) "IDX_action_au_r_c_u_d" btree (act_user_id, repo_id, created_unix, user_id, is_deleted) "IDX_action_comment_id" btree (comment_id) "IDX_action_r_u_d" btree (repo_id, user_id, is_deleted) "idx_action_c_u_d" btree (created_unix DESC, user_id, is_deleted) ``` https://explain.depesz.com/s/YJuyr The start page feels better.

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

Is that fast enough now?

@zeripath commented on GitHub (Sep 2, 2022): Is that fast enough now?

GiteaMirror commented

@somera commented on GitHub (Sep 2, 2022):

Is that fast enough now?

Yes! Thx.

@somera commented on GitHub (Sep 2, 2022): > Is that fast enough now? Yes! Thx.

GiteaMirror commented

2025-11-02 08:41:01 -06:00

@somera commented on GitHub (Sep 2, 2022):

Should I remove the index

"idx_action_c_u_d" btree (created_unix DESC, user_id, is_deleted)

and wait for the fix release?

@somera commented on GitHub (Sep 2, 2022): Should I remove the index `"idx_action_c_u_d" btree (created_unix DESC, user_id, is_deleted)` and wait for the fix release?

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

OK, now Gitea won't start up with those indexes like that due to a bug in xorm - so we have two problems:

We need to fix xorm to tolerate indexes that don't match the xorm pattern.
I guess we need to add this index by default
- One problem is that xorm can't express DESCing indexes so we should check if the DESC is necessary
- Another is that I'm not sure how applicable this index is to smaller dbs

My large "test" db "only" has 42569 rows in action so I guess this is why I didn't see this.

Could you check if this is still fast:

CREATE INDEX action_c_u_d ON action (created_unix, user_id, is_deleted)

If so that will make fixing this easier.

@zeripath commented on GitHub (Sep 2, 2022): OK, now Gitea won't start up with those indexes like that due to a bug in xorm - so we have two problems: * We need to fix xorm to tolerate indexes that don't match the xorm pattern. * I guess we need to add this index by default * One problem is that xorm can't express DESCing indexes so we should check if the DESC is necessary * Another is that I'm not sure how applicable this index is to smaller dbs My large "test" db "only" has 42569 rows in `action` so I guess this is why I didn't see this. Could you check if this is still fast: ``` CREATE INDEX action_c_u_d ON action (created_unix, user_id, is_deleted) ``` If so that will make fixing this easier.

GiteaMirror commented

2025-11-02 08:41:01 -06:00

@zeripath commented on GitHub (Sep 2, 2022):

Yes you will need to remove the index or at least remove it in between restarts.

@zeripath commented on GitHub (Sep 2, 2022): Yes you will need to remove the index or at least remove it in between restarts.

GiteaMirror commented

2025-11-02 08:41:01 -06:00

@zeripath commented on GitHub (Sep 2, 2022):

OK here's the XORM fix: https://gitea.com/xorm/xorm/pulls/2174

Now actually I think we should not drop irregular indices as it is likely that the user has added these deliberately but I guess we should discuss that in another PR.

@zeripath commented on GitHub (Sep 2, 2022): OK here's the XORM fix: https://gitea.com/xorm/xorm/pulls/2174 Now actually I think we should not drop irregular indices as it is likely that the user has added these deliberately but I guess we should discuss that in another PR.

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

@somera I've got a few more indexes to check:

CREATE "IDX_action_c_r_u_d" ON action (created_unix, repo_id, user_id, is_deleted)

and

CREATE "IDX_action_c_u_d_r" ON action (created_unix, user_id, is_deleted, repo_id)

If either of those are faster than IDX_action_c_u_d it would be helpful to know.

@zeripath commented on GitHub (Sep 2, 2022): @somera I've got a few more indexes to check: ``` CREATE "IDX_action_c_r_u_d" ON action (created_unix, repo_id, user_id, is_deleted) ``` and ``` CREATE "IDX_action_c_u_d_r" ON action (created_unix, user_id, is_deleted, repo_id) ``` If either of those are faster than `IDX_action_c_u_d` it would be helpful to know.

GiteaMirror commented

@somera commented on GitHub (Sep 2, 2022):

OK, now Gitea won't start up with those indexes like that due to a bug in xorm - so we have two problems:

We need to fix xorm to tolerate indexes that don't match the xorm pattern.

I guess we need to add this index by default

One problem is that xorm can't express DESCing indexes so we should check if the DESC is necessary

Another is that I'm not sure how applicable this index is to smaller dbs

My large "test" db "only" has 42569 rows in action so I guess this is why I didn't see this.

Could you check if this is still fast:
CREATE INDEX action_c_u_d ON action (created_unix, user_id, is_deleted)
If so that will make fixing this easier.

Sorry, I didn't see the question.

Here the plan for the index: https://explain.depesz.com/s/JfF9

But the speed is same like with: CREATE INDEX IDX_action_c_u_d ON action (created_unix DESC, user_id, is_deleted)

@somera commented on GitHub (Sep 2, 2022): > OK, now Gitea won't start up with those indexes like that due to a bug in xorm - so we have two problems: > > * We need to fix xorm to tolerate indexes that don't match the xorm pattern. > * I guess we need to add this index by default > > * One problem is that xorm can't express DESCing indexes so we should check if the DESC is necessary > * Another is that I'm not sure how applicable this index is to smaller dbs > > My large "test" db "only" has 42569 rows in `action` so I guess this is why I didn't see this. > > Could you check if this is still fast: > > ``` > CREATE INDEX action_c_u_d ON action (created_unix, user_id, is_deleted) > ``` > > If so that will make fixing this easier. Sorry, I didn't see the question. Here the plan for the index: https://explain.depesz.com/s/JfF9 But the speed is same like with: CREATE INDEX IDX_action_c_u_d ON action (created_unix DESC, user_id, is_deleted)

GiteaMirror commented

@zeripath commented on GitHub (Sep 2, 2022):

excellent so the DESC is unnecessary.

What about the repo_id containing ones?

@zeripath commented on GitHub (Sep 2, 2022): excellent so the DESC is unnecessary. What about the repo_id containing ones?

GiteaMirror commented