Performance issues are encountered when executing the user query #706

New Issue

Open

opened 2024-02-24 14:05:56 +00:00 by sevichecc · 0 comments

sevichecc commented

2024-02-24 14:05:56 +00:00

Your setup

OTP

Extra details

No response

Version

No response

PostgreSQL version

What were you trying to do?

Performance issues are encountered when executing the following query, which takes a long time (90 seconds and even more) , and stop the Akkoma instance:

STATEMENT:  SELECT distinct split_part(u0."nickname", '@', 2) FROM "users" AS u0 WHERE (u0."local" != $1)

$1 = 't'

And I used the EXPLAIN ANALYZE command to analyze the query, and the execution plan is as follows:

HashAggregate  (cost=92969.54..102240.85 rows=354247 width=32)
  Group Key: (regexp_split_to_array((nickname)::text, '\@'::text, 'i'::text))[2]
  Planned Partitions: 16
  ->  Seq Scan on users u0  (cost=0.00..79671.44 rows=354247 width=32)
        Filter: (NOT local)
JIT:
  Functions: 6
  Options: Inlining false, Optimization false, Expressions true, Deforming true

From the execution plan, it can be seen that the main cost of the query lies in the hash aggregate operation and the sequential scan operation.

What did you expect to happen?

I hope the performance of this query can be optimized to reduce the query time.

What actually happened?

This query takes over 90 seconds and interrupts the program execution

Logs

```
[debug] QUERY ERROR source="users" db=15029.4ms queue=0.2ms idle=996.1ms
                                                              SELECT distinct split_part(u0."nickname", '@', 2) FROM "users" AS u0 WHERE (u0."local" != $1) [true]
[error] GenServer Pleroma.Stats terminating
                                                              ** (Postgrex.Error) ERROR 57014 (query_canceled) canceling statement due to user request
                                                                  (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
                                                                  (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:828: Ecto.Adapters.SQL.execute/6
                                                                  (ecto 3.9.5) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
                                                                  (ecto 3.9.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
                                                                  (pleroma 3.9.3-0-g9d7c877) lib/pleroma/stats.ex:73: Pleroma.Stats.calculate_stat_data/0
                                                                  (pleroma 3.9.3-0-g9d7c877) lib/pleroma/stats.ex:121: Pleroma.Stats.handle_continue/2
                                                                  (stdlib 4.2) gen_server.erl:1123: :gen_server.try_dispatch/4
                                                                  (stdlib 4.2) gen_server.erl:865: :gen_server.loop/7
                                                              Last message: {:continue, :calculate_stats}
                                                              State: nil
```

Severity

I cannot use the software

Have you searched for this issue?

I have double-checked and have not found this issue mentioned anywhere.

### Your setup OTP ### Extra details _No response_ ### Version _No response_ ### PostgreSQL version 14 ### What were you trying to do? Performance issues are encountered when executing the following query, which takes a long time (90 seconds and even more) , and stop the Akkoma instance: ```sql STATEMENT: SELECT distinct split_part(u0."nickname", '@', 2) FROM "users" AS u0 WHERE (u0."local" != $1) ``` $1 = 't' And I used the EXPLAIN ANALYZE command to analyze the query, and the execution plan is as follows: ```sql HashAggregate (cost=92969.54..102240.85 rows=354247 width=32) Group Key: (regexp_split_to_array((nickname)::text, '\@'::text, 'i'::text))[2] Planned Partitions: 16 -> Seq Scan on users u0 (cost=0.00..79671.44 rows=354247 width=32) Filter: (NOT local) JIT: Functions: 6 Options: Inlining false, Optimization false, Expressions true, Deforming true ``` From the execution plan, it can be seen that the main cost of the query lies in the hash aggregate operation and the sequential scan operation. ### What did you expect to happen? I hope the performance of this query can be optimized to reduce the query time. ### What actually happened? This query takes over 90 seconds and interrupts the program execution ### Logs ````shell ``` [debug] QUERY ERROR source="users" db=15029.4ms queue=0.2ms idle=996.1ms SELECT distinct split_part(u0."nickname", '@', 2) FROM "users" AS u0 WHERE (u0."local" != $1) [true] [error] GenServer Pleroma.Stats terminating ** (Postgrex.Error) ERROR 57014 (query_canceled) canceling statement due to user request (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1 (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:828: Ecto.Adapters.SQL.execute/6 (ecto 3.9.5) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4 (ecto 3.9.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3 (pleroma 3.9.3-0-g9d7c877) lib/pleroma/stats.ex:73: Pleroma.Stats.calculate_stat_data/0 (pleroma 3.9.3-0-g9d7c877) lib/pleroma/stats.ex:121: Pleroma.Stats.handle_continue/2 (stdlib 4.2) gen_server.erl:1123: :gen_server.try_dispatch/4 (stdlib 4.2) gen_server.erl:865: :gen_server.loop/7 Last message: {:continue, :calculate_stats} State: nil ``` ```` ### Severity I cannot use the software ### Have you searched for this issue? - [x] I have double-checked and have not found this issue mentioned anywhere.

sevichecc added the

bug

label 2024-02-24 14:05:56 +00:00

No Milestone

No project

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: AkkomaGang/akkoma#706

No description provided.