pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table scan on 15.2
8+ messages / 3 participants
[nested] [flat]

* Table scan on 15.2
@ 2023-03-15 15:17  Arthur Ramsey <arthur.ramsey@code42.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Arthur Ramsey @ 2023-03-15 15:17 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 15:20  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Arthur Ramsey <arthur.ramsey@code42.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2023-03-15 15:20 UTC (permalink / raw)
  To: Arthur Ramsey <arthur.ramsey@code42.com>; pgsql-general

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 15:53  Arthur Ramsey <arthur.ramsey@code42.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Arthur Ramsey @ 2023-03-15 15:53 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general

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
>
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 16:03  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Arthur Ramsey <arthur.ramsey@code42.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2023-03-15 16:03 UTC (permalink / raw)
  To: Arthur Ramsey <arthur.ramsey@code42.com>; +Cc: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 16:10  Arthur Ramsey <arthur.ramsey@code42.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Arthur Ramsey @ 2023-03-15 16:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 16:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Arthur Ramsey <arthur.ramsey@code42.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2023-03-15 16:18 UTC (permalink / raw)
  To: Arthur Ramsey <arthur.ramsey@code42.com>; +Cc: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 16:24  Arthur Ramsey <arthur.ramsey@code42.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Arthur Ramsey @ 2023-03-15 16:24 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Table scan on 15.2
@ 2023-03-15 18:35  Arthur Ramsey <arthur.ramsey@code42.com>
  parent: Arthur Ramsey <arthur.ramsey@code42.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Arthur Ramsey @ 2023-03-15 18:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

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

>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2023-03-15 18:35 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-15 15:17 Table scan on 15.2 Arthur Ramsey <arthur.ramsey@code42.com>
2023-03-15 15:20 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-15 15:53   ` Arthur Ramsey <arthur.ramsey@code42.com>
2023-03-15 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-15 16:10   ` Arthur Ramsey <arthur.ramsey@code42.com>
2023-03-15 16:18     ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-15 16:24       ` Arthur Ramsey <arthur.ramsey@code42.com>
2023-03-15 18:35         ` Arthur Ramsey <arthur.ramsey@code42.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox