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 1t0ZoK-00CA2d-Ms for pgsql-in-general@arkaria.postgresql.org; Tue, 15 Oct 2024 05:09:32 +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 1t0ZoI-003GD4-6n for pgsql-in-general@arkaria.postgresql.org; Tue, 15 Oct 2024 05:09:30 +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 1t0ZoH-003GCt-Sd; Tue, 15 Oct 2024 05:09:30 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0ZoF-0017tC-BJ; Tue, 15 Oct 2024 05:09:29 +0000 Received: by mail-ej1-x634.google.com with SMTP id a640c23a62f3a-a9a26a5d6bfso61478766b.1; Mon, 14 Oct 2024 22:09:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728968966; x=1729573766; 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=56s96If++rE24YaVwnGvv4F/JmUEvmZ3hkflqgs3d6c=; b=MjJ2qy8gyTVJ3LgdAq0Zvsi+WPpAL5SnZRSJ1Xgi3PsfjU2oPizPcOLoUqm2R9q66/ /jmsf7MZtoGZ63UhaowCo9UsLUcJvOccwP0Sqex43+ylLvzE92EvzBjWwY7hyd87udiH TZ7ctEfu8rPXUGbyqW/6qgAxXglVp5lGMKeVDxMr7k98XB5kLc7iNfxui3GbTawefKer 2jFhccmZWyWGjyZ9DyoyurSYahecJr7G01sLYKtEMrrzUFb4xlEkjOJUACihUoi1rItU XQPJeLJL6PLkZEnoj1dk1PxK/q3qOrnXajCaKmLjG69D0IqJvXVQ6o8yMekrCxZLy5sC 86NA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728968966; x=1729573766; 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=56s96If++rE24YaVwnGvv4F/JmUEvmZ3hkflqgs3d6c=; b=PpJuoVgW/oL9HPf87/Ic57+vEVmgYTrZ6Y+ENEfg56UXJV99FeegtfQ/g+kgIOUusq MpQrHu0QjDPrvwshTmZtZtUAVjzUW9bHXkJAZB8CaULRnP9CuD6QCPDHY3IqFWpZYWKQ gjTn9AqH/An90aBN772My9PvQtBBYoygTV02lz8nzyij9anEGOcjFjgBwPAIZ36XIz4y FYJFmVwEFDK4MPYfi59KY6pq1ujhIOKzFvKvvMm3cM0eXeLDhknkClc6u++kmAIwAkk/ yH/o5tEJWI51hlObwp83hJQW8RG0RshrT+IBJvGY0GqxAHvBjQEMP2uN8Va0gBajsCEc v0VA== X-Gm-Message-State: AOJu0YwI139kAeqGPX/dQTyDzgYGMHbH31VG1fKk6jnylz+mEhJAQxPQ epY7A/ViKpuKQsgxyI0xUHgIIcNagpOCAee1oN76v6wLpZ1TUSjlpkz9K5oovoSrmPnZU9XKeIi f33/2rRO4ymX0XW4elnEptNwig0U3YA== X-Google-Smtp-Source: AGHT+IFgPzVuj6QP4yGsyP2jb1iUc4D39yMme9XETcEW0V+sl/PUG+0p3KXKAaFYJIZCdJJ5zcvgLppXccoXuDG+tEU= X-Received: by 2002:a17:907:1b24:b0:a99:4601:b9d8 with SMTP id a640c23a62f3a-a99e3ea6502mr1078006566b.63.1728968965310; Mon, 14 Oct 2024 22:09:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Tue, 15 Oct 2024 10:39:13 +0530 Message-ID: Subject: Re: Inefficient use of index scan on 2nd column of composite index during concurrent activity To: pgsql-in-general@postgresql.org Cc: PostgreSQL mailing lists Content-Type: multipart/alternative; boundary="0000000000000153de06247cf545" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000153de06247cf545 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, 11 Oct, 2024, 23:33 Durgamahesh Manne, wrote: > > > 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 tha= t. >> >> 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, bu= t >> 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 ro= ws=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 ro= ws=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) whe= n > 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 > Hi PGDG In oracle Example for better understanding to you 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 in oracle . this is the example gathered for reference In the similar way Do we have anything in postgres like oracle ? Regards, Durga Mahesh > --0000000000000153de06247cf545 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, 11 Oct, 2024, 23:33 Durgamahesh Manne, <maheshpostgres9@gmail.com>= wrote:


On Fri, Oct 11, 2024 at 9:57=E2=80=AFPM Greg Sabino Mullane &l= t;htamfids@gmail.com> wrote:

=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=A0Partitioned 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| Compression | 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=A0externalbetid =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=A0placedon =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| times= tamp 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"pmk_test" PRIMARY KEY, btree (id, placedon) = REPLICA IDENTITY
if use this=C2=A0 (id,placedon) when running sele= ct 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=3D= 0.14..42.14 rows=3D19 width=3D1355) (actual time=3D0.177..0.186 rows=3D1 lo= ops=3D1)
=C2=A0 =C2=A0-> =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) (ac= tual time=3D0.009..0.009 rows=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_p2023_08_id_idx on test_p2023_08 test_2 =C2=A0(cos= t=3D0.14..2.38 rows=3D1 width=3D1848) (actual time=3D0.005..0.005 rows=3D0 = loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D '42= 34'::text)
=C2=A0Planning Time: 0.1= 00 ms
=C2=A0Execution Time: 0.40 ms
=
>>>>>=C2=A0 =C2=A0 if i= change constraint order (placedon,id) then=C2=A0 in this case=C2=A0

I could = see same index scan with explain analyze=C2=A0 for 1 call or 2 calls
<= div class=3D"gmail_quote">
Here concurr= ent mode means you are already aware (no of calls increases concurrently)
Sudden cpu spike i have observed which is un= usual(more than needed)=C2=A0 =C2=A0when no of calls increased concurrently= on that query=C2=A0

Based on that info=C2=A0 i suspected that query plan chang= ed hence raised question here=C2=A0 this is what i faced with mentioned col= umns order related to problem


Example for be= tter understanding to you=C2=A0
in oracle= =C2=A0
CREATE INDEX idx_orders_customer_dat= e ON orders (customer_id, order_date);
= SELECT /*+ INDEX(orders idx_orders_customer_date) */ *
FROM orders
WHERE order_date =3D '2024-01-01';<= br>
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&= #39;t explain more than this much


Regards,
Durga Mahesh=C2=A0
<= /div>



<= div dir=3D"auto">FROM orders




--0000000000000153de06247cf545--