Yes, I forgot to mention I did a REINDEX DATABASE and ANALYZE.
On Wed, Mar 15, 2023 at 10:20 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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
