Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnsl1-00FkEh-6M for pgsql-general@arkaria.postgresql.org; Sat, 23 Mar 2024 04:13:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rnskz-008ogG-9S for pgsql-general@arkaria.postgresql.org; Sat, 23 Mar 2024 04:13:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnsky-008og7-VG for pgsql-general@lists.postgresql.org; Sat, 23 Mar 2024 04:13:21 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rnskw-0061Ae-N3 for pgsql-general@lists.postgresql.org; Sat, 23 Mar 2024 04:13:20 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 42N4DGpF1242762; Sat, 23 Mar 2024 00:13:16 -0400 From: Tom Lane To: arun chirappurath cc: pgsql-general@lists.postgresql.org Subject: Re: Seq scan vs index scan In-reply-to: References: Comments: In-reply-to arun chirappurath message dated "Sat, 23 Mar 2024 09:25:16 +0530" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1242760.1711167196.1@sss.pgh.pa.us> Date: Sat, 23 Mar 2024 00:13:16 -0400 Message-ID: <1242761.1711167196@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk arun chirappurath writes: > I have a table named users with index on user name. > ... > When I try to do below select query it's taking seq scan and query returns > in 5ms. 5ms is an okay runtime, I would think. Is the table empty? > I am trying to force query to use indexes using query hints. The way to force it would be enable_seqscan = off. I would not be surprised to hear that that makes it slower, though. The planner will prefer not to use an index if it thinks that will be slower, and in this case it sounds like that could be right. (Don't ever assume that what the planner does with a small table is what it will do with a large table.) regards, tom lane