Table scan on 15.2

From: Arthur Ramsey <arthur(dot)ramsey(at)code42(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Table scan on 15.2
Date: 2023-03-15 15:17:37
Message-ID: CAEJqy4WBz=C67tJ4Sjq_T0yTwO0b7_ye34xVY7cxrSDXqnucjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-03-15 15:20:22 Re: Table scan on 15.2
Previous Message magog002 2023-03-15 13:51:37 Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour