Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcSva-00026O-3d for pgsql-general@arkaria.postgresql.org; Wed, 15 Mar 2023 15:20:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pcSvY-0006st-4f for pgsql-general@arkaria.postgresql.org; Wed, 15 Mar 2023 15:20:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcSvX-0006sj-2W for pgsql-general@lists.postgresql.org; Wed, 15 Mar 2023 15:20:31 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcSvT-0003YA-6j for pgsql-general@postgresql.org; Wed, 15 Mar 2023 15:20:30 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 7F55F5C056F; Wed, 15 Mar 2023 11:20:24 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Wed, 15 Mar 2023 11:20:24 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1678893624; x=1678980024; bh=eYvnlWkx4vZ8t74CbYzVKEbB3UZel3c1qkq KCB7P5N4=; b=EfDu2EgFpDksDVfj0muc9ZKSEwS/KVy9SOqaySYZu6VEgRvyPON 4o5J8hqzm+2ios0NpISn+ZYcQUcR1kS1w3IxjCATowEfe06G+v2uSbyDcKbXLzO3 8pVJ4Twlfluz2MUyjJm7GRoTLlDixrom9aYjaP1DM5J0giFYavYREOh6FdOlhgrp PE17d5sJfV/9uTxBlSsqyNRKVIixAAJxXcOY3xs9sqP0S971laVL8tQUeGvdJv2L 03MUsd0lYyhU8WulUYcd+AbagxWr8bgHVj92V5R/hequ3t365SYir+G0ePKh7XjW OW4fi96AeSEat2dtFUFXIUHyNB4q6DmgkWA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1678893624; x= 1678980024; bh=eYvnlWkx4vZ8t74CbYzVKEbB3UZel3c1qkqKCB7P5N4=; b=t uQPz9iC/RIuy65gqYNsi/VAGAHWHm8xE+3gpYJi5r6eJsEZDG231M4DaCS26y7jh TuE38XfBpKzrGRy2lo6N9cQCglnGkWeDcUwBiMcGgu0zVuHMiVpwVenlpamgZi3J 3gQxniwIiRz5l5+jphjPzEoPtrINY8jN4f8nAIIyrMScJjuMlEWLk0pp/cXKpWvP e+yO/ziXmRZ+/rZ9m4Qr7RW+CeeFsooOWSTs4mOR5Gqk5hsfg4mWYYRwaaQvA+Aq dgdlTSPk7nlY8iFLaUZZ3w2w8uT1kwPNRC8F19Cx5prV6Xm+oq4VXpp6dI4HTtco dihXiuMedcm1srKDxhOrg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvddvkedgjeegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpedugfeuleegveffhfegleejhefhtedtgeeileeggfeh fefffeevgeejtdeutefftdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 15 Mar 2023 11:20:23 -0400 (EDT) Message-ID: <724ef849-740a-d987-8a0d-cd5f42bc2aa4@aklaver.com> Date: Wed, 15 Mar 2023 08:20:22 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: Table scan on 15.2 To: Arthur Ramsey , pgsql-general@postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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