On 3/15/23 08:17, Arthur Ramsey wrote:
I've upgraded from 12.11 to 15.2 and I'm seeing this query now use a
sequential scan which is taking 500ms instead of < 2ms. If I disable
sequential scans then it performs as well as 12.11.
Did you run ANALYZE on the database/table in the new 15.2 instance?
Schema:
Table "public.t_user"
Column |Type| Collation | Nullable | Default
---------------------------+------------------------+-----------+----------+--------------
uid | character varying(36)| | not null |
username| character varying(346) | | not null |
tenant_id | character varying(36)| | not null |
active| boolean| || true
watchlists| text[] | | not null | '{}'::text[]
authorized_activity_lists | text[] | | not null | '{}'::text[]
Indexes:
"user_pkey" PRIMARY KEY, btree (uid)
"idx_t_user__tenant_id" btree (tenant_id)
"idx_t_user__username" btree (username)
"idx_t_user__username__upper" btree (upper(username::text))
Referenced by:
TABLE "t_cloud_alias" CONSTRAINT "t_cloud_alias_fk_t_user_uid" FOREIGN
KEY (user_uid) REFERENCES t_user(uid) ON DELETE CASCADE
12.11:
Index Scan using idx_t_user__username__upper on t_user user0_
(cost=0.42..8164.72 rows=1000 width=113) (actual time=0.316..1.014
rows=36 loops=1)
Index Cond: (upper((username)::text) = ANY ('{[redacted]}'::text[]))
Planning Time: 1.434 ms
Execution Time: 1.038 ms
15.2:
Seq Scan on t_user user0_ (cost=2.50..39152.22 rows=800678 width=761)
(actual time=19.148..514.658 rows=36 loops=1)
Filter: (upper((username)::text) = ANY ('{[redacted}'::text[]))
Rows Removed by Filter: 806235
Planning Time: 0.556 ms
Execution Time: 514.675 ms
This is a list of distinct values from the IN clause and their count
(1000 total values).
1
1
1
1
1
1
1
2
2
2
3
4
6
7
7
10
10
11
12
14
14
22
22
23
23
25
29
29
34
39
50
56
67
75
137
258
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
