Thread: Table scan on 15.2

Table scan on 15.2

From
Arthur Ramsey
Date:
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

Re: Table scan on 15.2

From
Adrian Klaver
Date:
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




Re: Table scan on 15.2

From
Arthur Ramsey
Date:
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:
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

Re: Table scan on 15.2

From
Tom Lane
Date:
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



Re: Table scan on 15.2

From
Arthur Ramsey
Date:
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

Re: Table scan on 15.2

From
Tom Lane
Date:
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



Re: Table scan on 15.2

From
Arthur Ramsey
Date:
That worked, thanks.  I was trying to find how to do that in pgsql.

Re: Table scan on 15.2

From
Arthur Ramsey
Date:
What's strange is that there is only 1 non-unique value in the column.