pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Arthur Ramsey <arthur.ramsey@code42.com>
To: pgsql-general@postgresql.org
Subject: Re: Table scan on 15.2
Date: Wed, 15 Mar 2023 08:20:22 -0700
Message-ID: <724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com> (raw)
In-Reply-To: <CAEJqy4WBz=C67tJ4Sjq_T0yTwO0b7_ye34xVY7cxrSDXqnucjg@mail.gmail.com>
References: <CAEJqy4WBz=C67tJ4Sjq_T0yTwO0b7_ye34xVY7cxrSDXqnucjg@mail.gmail.com>

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






view thread (8+ messages)  latest in thread

Message-ID: <724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com>
Permalink:  ../724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com/
Also on:    postgresql.org/message-id/724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, arthur.ramsey@code42.com
  Subject: Re: Table scan on 15.2
  In-Reply-To: <724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com>
  References: <CAEJqy4WBz=C67tJ4Sjq_T0yTwO0b7_ye34xVY7cxrSDXqnucjg@mail.gmail.com> <724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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