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