Re: Table scan on 15.2

Yes, I forgot to mention I did a REINDEX DATABASE and ANALYZE.

On Wed, Mar 15, 2023 at 10:20 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>