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 1sxfP1-00BXn0-NI for pgsql-in-general@arkaria.postgresql.org; Mon, 07 Oct 2024 04:31: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 1sxfP0-005n8K-1m for pgsql-in-general@arkaria.postgresql.org; Mon, 07 Oct 2024 04:31:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sxfOz-005n8C-On for pgsql-in-general@lists.postgresql.org; Mon, 07 Oct 2024 04:31:21 +0000 Received: from mail-ej1-x631.google.com ([2a00:1450:4864:20::631]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sxfOt-002tdM-DY for pgsql-in-general@postgresql.org; Mon, 07 Oct 2024 04:31:20 +0000 Received: by mail-ej1-x631.google.com with SMTP id a640c23a62f3a-a993302fa02so209893866b.0 for ; Sun, 06 Oct 2024 21:31:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728275473; x=1728880273; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=6YXilRTnyf4cd9i18dGjoLsGOoJIVA8hraFJ61Z63ow=; b=ZfqkE6lj4ZDrGUUqlLvUdfUlgRVeiJ2z6sK5uF1BvJjFfU6jB4qiuBwDz2HR75xgOC 080n29YDpRJluVog0QBDDAtylIb7rfYfruDK3xMBVYgNtnxcU+B8BIdzGGkIb48O0fb5 HmAhP4xAILPRTdtjzQJvAN03cUEgpQ4s+rOxVPa+sDQacCt3VZiSvhX9Zt9d3srbfdty PjGs/w56Oi57MRHwm8vnWjx/swM+ZE7KLJcBpAiauEwIBTYxoafLCheyyM6qeX3jKGLw uOgN93a04CpVeyN2/H6Smv0rgNoHHVT/5S2pbE5C8oT32gZ1xmmvKgH/QDNQG+hKHrHr Fgcw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728275473; x=1728880273; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=6YXilRTnyf4cd9i18dGjoLsGOoJIVA8hraFJ61Z63ow=; b=l2mqSp3Q0tyTgzrLI1LSZEg26sg+45ghWwRdsEiIdMzBRyHh+9Z0DlrJOXvpHfUPLp NGzxxb0V1NQgBjUcQg9xAhPAI54hCSPSMfFFH33Dtj/D1tZut29cMkcj19Pa2ReZjvu/ /0UzabetH0SDRkLtk3l6pglXWA4DtvUpzjlI7DHohxC1wnP0WPXXJGcICDOIlOosiF5Q 8Du/tKWfDt2ay2FdSlDkr/oVUjUq+XoHcD2pG1+6kJublUq9fiW61Mby+e9nrFT7sIHW qR2wKRurRBVFjx0fyi2sQDjt4IquA8QFKZc4WZU+NoVz0a7XXbLjSuHH5G2exNEqmfSQ 0wNg== X-Gm-Message-State: AOJu0YwvM0avQuSddnj8YhFey0qAMOW3kJnvPim3k2aWyNQOTO/Elw5K Va+4u8Wu+TcAKcOVa+E0KidoOkdMXdVQWJc/LQJlv+oLnGGzURdnnEdGoOrE9cGlR75mOaD7ogJ 6vzEEINulqYAfSBxdqcH9wJmBNMEPpQ== X-Google-Smtp-Source: AGHT+IErpXf+M+nK9Bvs4SYAhIaLH3qGaIdI16R/UtUlJ4IEyKgxnTz0p0JqI8m+RFvpfRE/yzZFpgofYfn1qtF6PSA= X-Received: by 2002:a17:906:6a28:b0:a99:36d1:49b5 with SMTP id a640c23a62f3a-a9936d1505cmr804756566b.14.1728275472625; Sun, 06 Oct 2024 21:31:12 -0700 (PDT) MIME-Version: 1.0 From: Durgamahesh Manne Date: Mon, 7 Oct 2024 10:01:00 +0530 Message-ID: Subject: Inefficient use of index scan on 2nd column of composite index during concurrent activity To: pgsql-in-general@postgresql.org Content-Type: multipart/alternative; boundary="0000000000009eb85b0623db7d81" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009eb85b0623db7d81 Content-Type: text/plain; charset="UTF-8" Hi team Second column of composite index not in use effectively with index scan when using second column at where clause I have composite index on (placedon,id) of test When quering select * from test where id = '4234'; Value of id changes and during concurrent activity and cpu utilization increased toomuch that i have observed which means query plan changed why I could see index scan with explain for it Is there any way to keep index scan for it during even on concurrency rather than seperate index on second column of composite index ? Hope everyone understand this Regards, Durga Mahesh --0000000000009eb85b0623db7d81 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi team=C2=A0

Second column of composite index not in use effectively with index scan= =C2=A0 when using second column at where clause

=
I have composite index on (placedon,id) of test=C2= =A0
When quering=C2=A0 select * from test where id = =3D '4234';
Value of id changes and during c= oncurrent activity and cpu utilization increased toomuch=C2=A0 that i have = observed which means query plan changed why

I could see index scan with explain for it=C2=A0
<= div dir=3D"auto">
Is there any way to keep index= scan for it during even on concurrency rather than seperate index on secon= d column of composite index ?

Hope everyone understand this=C2=A0

=
Regards,
Durga Mahesh=C2=A0
<= div dir=3D"auto">
--0000000000009eb85b0623db7d81--