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

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

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

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)
Recheck Cond: (upper((username)::text) = ANY ('{[redacted]'::text[]))
Heap Blocks: exact=36
-> Bitmap Index Scan on idx_t_user__username__upper
(cost=0.00..19364.50 rows=800678 width=0) (actual time=0.356..0.356
rows=36 loops=1)
Index Cond: (upper((username)::text) = ANY ('{redacted}'::text[]))
Planning Time: 1.187 ms
Execution Time: 1.067 ms

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

That worked, thanks. I was trying to find how to do that in pgsql.

What's strange is that there is only 1 non-unique value in the column.