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 1szJvy-005Qrh-00 for pgsql-general@arkaria.postgresql.org; Fri, 11 Oct 2024 18:00:14 +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 1szJvv-008oTX-IX for pgsql-general@arkaria.postgresql.org; Fri, 11 Oct 2024 18:00:11 +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 1szJvv-008oSv-67 for pgsql-general@lists.postgresql.org; Fri, 11 Oct 2024 18:00:11 +0000 Received: from mail-lj1-x232.google.com ([2a00:1450:4864:20::232]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1szJvo-000YEn-7u for pgsql-general@postgresql.org; Fri, 11 Oct 2024 18:00:10 +0000 Received: by mail-lj1-x232.google.com with SMTP id 38308e7fff4ca-2fad6de2590so34742281fa.0 for ; Fri, 11 Oct 2024 11:00:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728669604; x=1729274404; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=YUVAaO/+HpxEEeU9DF1iKVU4iKu1ZrCvrJgH8gbK7HE=; b=Yrbt0fBkanpAydb6Xk3kIihnNd6WQnoikJZZKDYSZXeUHYVof2ql0JwhNDY7QzF/p4 AHnBGFF6DUrOTAfGxbf8YLHyBT35qY7HKqYsyS9an4qEh/txgr0pute5eZZVUU1mIl11 rrLg//xF8h8ZuaMQPJfxFdG2GDMWKmb9gNXbWh88qpFs5ncqSfb/jU19MfrmLYBQizQ5 oFRfrM2PfxbDsxEkFeuBEHhMICVjabpugrgDNNDzh83JVQ7sYbzgF6NMkMBltEg8Y2ko 1qsgOdKsAcgFKWlkzah7bpoqUFnBnr1JMsg2Q5YHZbLB+mv8iSkiIEjUv0S4rRlLoBtw 2HYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728669604; x=1729274404; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=YUVAaO/+HpxEEeU9DF1iKVU4iKu1ZrCvrJgH8gbK7HE=; b=NENObFl6FIwDbiDmfRTdwKFpDhCviyN93PtbiGc2qEKFu+GITNB75+k+gIDogXzJ6N 76Kpv4/nx4kb2xODb8cSvwDiAUzrSyR5b8SR9TJqCw68cyp9uI+9Ml3cPQfYumxSuvux wmtlJBLOAy8FSiym01FjmUTV/tZydeyFb2nPIVEyKMpk1alCBIFNsu1z6okxbVhOd5jk f1/P7ccF/VVbzgskfi04rZhUVaJZYkYr3bNcqVmqDBEnEsg7pVt05E1BY6Mq4g+P+TJo vIq2sgL66jwFJ73i7z3wU7HxJt/dXDO5K6/0wjzYcdoy4ySGq2DrARbtKUBKIoEB7275 TU6g== X-Gm-Message-State: AOJu0YyB3Vv/KTFSLKxb9HkWINwOH1tX6Yu9D81emPPrTHbTZyp9HMCq pz42ozP94mi82dGwsXSozE+EtQLOS+z7jSedjbVmnNmhKMGwRpPdDTofOne0+KWotovLVVqqAL7 LH6ERL1xwebbm4u7cVsW7gMDlRsY= X-Google-Smtp-Source: AGHT+IHT0727ogYv3MHxIjzHofD3Tt1VK4xHEZB0LfZwwdB5j5AJB2K0Y3yhQWfY6uAqsxq80Or62tg3jBKLMntM+Mc= X-Received: by 2002:a2e:be09:0:b0:2ef:2ba5:d214 with SMTP id 38308e7fff4ca-2fb3f170077mr4067821fa.4.1728669603807; Fri, 11 Oct 2024 11:00:03 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Fri, 11 Oct 2024 23:33:15 +0530 Message-ID: Subject: Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity To: Greg Sabino Mullane Cc: PostgreSQL mailing lists Content-Type: multipart/alternative; boundary="000000000000ab4bca06243741a0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ab4bca06243741a0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Oct 11, 2024 at 9:57=E2=80=AFPM Greg Sabino Mullane wrote: > On Fri, Oct 11, 2024 at 9:28=E2=80=AFAM Durgamahesh Manne < > maheshpostgres9@gmail.com> wrote: > >> composite key (placedon,id) >> In concurrent mode if i use id at where clause then query plan for that >> id column changes >> >> How to mitigate it rather than use seperate index for id to continue >> without change in query plan (index scan) during concurrent activity >> > > Why the focus on "concurrent mode"? Perhaps explain what you mean by that= . > > Speaking of explain, it might help if you show us the explain plans and > how they are not coming out how you want. Also the table definitions, but > feel free to not show columns unrelated to the problem. > > Cheers, > Greg > > Hi Greg Thanks for your quick response Partitioned table "test" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------------------+--------------------------+-----------+----------+---= ------+----------+-------------+--------------+------------- id | bigint | | not null | | plain | | | externalbetid | text | | | | extended | | | externalsystem | text | | | | extended | | | placedon | timestamp with time zone | | not null | | plain | | | txnstep | integer | | | | plain | | | txnstage | text | | | | extended | | | txnstatus | text | | | | extended | | | "pmk_test" PRIMARY KEY, btree (id, placedon) REPLICA IDENTITY if use this (id,placedon) when running select query then no issues bez select picks up first column of composite key select * from test where id =3D '4234'; Append (cost=3D0.14..42.14 rows=3D19 width=3D1355) (actual time=3D0.177..= 0.186 rows=3D1 loops=3D1) -> Index Scan using test_p2023_07_id_idx on test_p2023_07 test_1 (cost=3D0.14..2.38 rows=3D1 width=3D1874) (actual time=3D0.009..0.009 rows= =3D0 loops=3D1) Index Cond: (id =3D '4234'::text) -> Index Scan using test_p2023_08_id_idx on test_p2023_08 test_2 (cost=3D0.14..2.38 rows=3D1 width=3D1848) (actual time=3D0.005..0.005 rows= =3D0 loops=3D1) Index Cond: (id =3D '4234'::text) Planning Time: 0.100 ms Execution Time: 0.40 ms >>>>> if i change constraint order (placedon,id) then in this case I could see same index scan with explain analyze for 1 call or 2 calls Here concurrent mode means you are already aware (no of calls increases concurrently) Sudden cpu spike i have observed which is unusual(more than needed) when no of calls increased concurrently on that query Based on that info i suspected that query plan changed hence raised question here this is what i faced with mentioned columns order related to problem Example for better understanding to you in oracle CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date); SELECT /*+ INDEX(orders idx_orders_customer_date) */ * FROM orders WHERE order_date =3D '2024-01-01'; I am not sure how this works . this is the example gathered for you I hope you can understand . Sorry i can't explain more than this much Regards, Durga Mahesh --000000000000ab4bca06243741a0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Fri, Oct 11, 2024 at 9:57=E2=80=AF= PM Greg Sabino Mullane <htamfids@g= mail.com> wrote:
On Fri, Oct 11, 2024 at 9:28=E2= =80=AFAM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
composite key (placedon,id)=C2=A0
In concurrent mode if= i use id at where clause then query plan for that id column changes=C2=A0<= /div>

How to mitigate it rather than use seperate index = for id to continue without change in query plan (index scan) during concurr= ent activity=C2=A0

Why the focus on "concurrent mode"? Perhaps explain what yo= u mean by that.

Speaking of explain, it might help= if you show us the explain plans and how they are not coming out how you w= ant. Also the table definitions, but feel free to not show columns unrelate= d to the problem.

Cheers,
Greg

Hi Greg=C2=A0
Thanks for= your quick response

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Partition= ed table "test"
=C2=A0 =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2= =A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | Collation | Nullable | Default | Storage =C2=A0| Com= pression | Stats target | Description
---------------------+------------= --------------+-----------+----------+---------+----------+-------------+--= ------------+-------------
=C2=A0id=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0ext= ernalbetid =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended = | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|
=C2=A0externalsystem =C2=A0 =C2=A0 =C2=A0| text = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0placedo= n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| timestamp with time zone | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = plain =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0txnstep =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|
=C2=A0txnstage =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| text =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
= =C2=A0txnstatus =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0 =C2=A0 =C2=A0"p= mk_test" PRIMARY KEY, btree (id, placedon) REPLICA IDENTITY
i= f use this=C2=A0 (id,placedon) when running select query then no issues bez= select picks up first column of composite key=C2=A0
select * from test where id =3D '4234';
=C2=A0Append =C2=A0(cost=3D0.14..42.14 rows=3D19 width= =3D1355) (actual time=3D0.177..0.186 rows=3D1 loops=3D1)
=C2=A0 =C2=A0-&= gt; =C2=A0Index Scan using test_p2023_07_id_idx on test_p2023_07 test_1 =C2= =A0(cost=3D0.14..2.38 rows=3D1 width=3D1874) (actual time=3D0.009..0.009 ro= ws=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D = '4234'::text)
=C2=A0 =C2=A0-> =C2=A0Index Scan using test_p20= 23_08_id_idx on test_p2023_08 test_2 =C2=A0(cost=3D0.14..2.38 rows=3D1 widt= h=3D1848) (actual time=3D0.005..0.005 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D '4234'::text)
=C2=A0Planning Time: 0.100 ms
=C2=A0Execution Tim= e: 0.40 ms

>>>>>=C2=A0 =C2=A0 if i change constraint order (pl= acedon,id) then=C2=A0 in this case=C2=A0

I could see same index scan with exp= lain analyze=C2=A0 for 1 call or 2 calls
Here concurrent mode means you are alrea= dy aware (no of calls increases concurrently)
Sudden cpu spike i have observed which is unusual(more than needed)=C2= =A0 =C2=A0when no of calls increased concurrently on that query=C2=A0
=

Based on th= at info=C2=A0 i suspected that query plan changed hence raised question her= e=C2=A0 this is what i faced with mentioned columns order related to proble= m


=
Example for better understanding to you=C2= =A0
in oracle=C2=A0
CREATE INDEX idx_orders_customer_date ON orders (customer_id, orde= r_date);
SELECT /*+ INDEX(orders idx_or= ders_customer_date) */ *
FROM ordersWHERE order_date =3D '2024-01-01';
I am not sure how this works . this is the example gathered for you=C2= =A0

I = hope you can understand=C2=A0 . Sorry i can't explain more than this mu= ch

Regards,
= Durga Mahesh=C2=A0
--000000000000ab4bca06243741a0--