Обсуждение: Table scan on 15.2

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@aklaver.com

Arthur Ramsey <arthur.ramsey@code42.com> writes:
> 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.

What does the EXPLAIN look like with seqscans disabled?

            regards, tom lane

Arthur Ramsey <arthur.ramsey@code42.com> writes:
> After a restart and seq disabled for session with no other load (same for
> plan with seq enabled).

>  Bitmap Heap Scan on t_user user0_  (cost=19567.17..58623.03 rows=800678
> width=761) (actual time=0.370..0.702 rows=36 loops=1)

So the problem is that awful rowcount estimate.  (I see that v12's
estimate wasn't exactly spot-on either, but it wasn't so bad as to
push the planner to use a seqscan.)  Hard to form an opinion on the
cause of that when you've redacted all the details of the index
condition, unfortunately.

It could be that increasing the table's statistics target and
re-ANALYZEing would help.

            regards, tom lane