Message-ID: <194ab08d4e0392152ed7dfd3fa4a6ea7de6250a6.camel@cybertec.at>
Subject: Re: sql statement not using all primary key values and poor
 performance
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Pang <jamespang886@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Fri, 23 Feb 2024 10:17:34 +0100
In-Reply-To: 
 <CAHgTRfe6e0KUOXBchzXhA3FG-Pn__OY6g301b_W+Zt3a6Xvhbg@mail.gmail.com>
References: 
	<CAHgTRfe6e0KUOXBchzXhA3FG-Pn__OY6g301b_W+Zt3a6Xvhbg@mail.gmail.com>
Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual;
 keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk
 bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC
 GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b
 lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1
 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ
 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59
 +AnW5l0HsQcY9ntFL/zEBOaonjdJf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/194ab08d4e0392152ed7dfd3fa4a6ea7de6250a6.camel%40cybertec.at>
Precedence: bulk

On Fri, 2024-02-23 at 15:20 +0800, James Pang wrote:
> =C2=A0 =C2=A0we have a SQL from Postgresql JDBC,=C2=A0 primary is based o=
n (bigint,varchar2,bigint),
> but from sql plan, it convert to ::numeric so=C2=A0the plan just use one =
"varchar"
> key column and use the other 2 bigint keys as filters. what's the cause a=
bout that ?=C2=A0
>=20
> =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 Table "test.xxxxxx"
> =C2=A0 =C2=A0 =C2=A0 Column =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0| Collation | Nullable | Default
> ------------------+--------------------------------+-----------+---------=
-+---------
>  =C2=A0xxxid =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 =C2=A0 =C2=A0 =C2=A0 | not null |
> =C2=A0paramname =C2=A0 =C2=A0 =C2=A0 =C2=A0| character varying(512) =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
> =C2=A0paramvalue =C2=A0 =C2=A0 =C2=A0 | character varying(1536) =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 sssid =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 =C2=A0 =C2=A0 =C2=A0 | not null |
> =C2=A0createtime =C2=A0 =C2=A0 =C2=A0 | timestamp(0) without time zone | =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
> =C2=A0lastmodifiedtime | timestamp(0) without time zone | =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
> =C2=A0mmmuuid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| character varying(32) =
=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|
> Indexes:
> =C2=A0 =C2=A0 =C2=A0 "pk_xxxxxx" PRIMARY KEY, btree (xxxid, paramname, ss=
sid)
> =C2=A0 =C2=A0 =C2=A0 "idx_xxxxxx_mmmuuid" btree (sssid, mmmuuid, paramnam=
e)
>=20
> SET extra_float_digits =3D 3
>=20
> duration: 7086.014 ms =C2=A0plan:
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Query Text: SELECT XXXFID, PARAMNAME, PARAMVA=
LUE, SSSID, CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XXXXXX WHERE =
=C2=A0( ( XXXID =3D $1 =C2=A0) ) =C2=A0AND =C2=A0( ( PARAMNAME =3D $2 =C2=
=A0) ) =C2=A0AND =C2=A0( ( SSSID =3D $3 =C2=A0) )
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Scan using pk_xxxxxx on test.xxxxxx =C2=
=A0(cost=3D0.57..2065259.09 rows=3D1 width=3D86) (actual time=3D7086.010..7=
086.011 rows=3D0 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Output: confid, paramname, paramvalue,=
 sssid, createtime, lastmodifiedtime, mmmuuid
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: ((xxxxxx.paramname)::text =
=3D 'cdkkifffff'::text)=C2=A0 =C2=A0<<< just use only one key instead all p=
rimary keys.
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (((xxxxxx.xxxid)::numeric =3D =
'18174044'::numeric) AND ((xxxxxx.sssid)::numeric =3D '253352'::numeric))=
=C2=A0 =C2=A0 <<< it's bigint but converted to numeric=C2=A0
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared read=3D1063470
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 I/O Timings: read=3D4402.029
>=20
> it's from JDBC, we saw this JDBC driver try to set=C2=A0extra_float_digit=
s =3D 3 before
> running the SQL ,does that make planner to convert bigint to numeric ?

Setting "extra_float_digits" is just something the JDBC driver does so as t=
o
not lose precision with "real" and "double precision" values on old version=
s
of PostgreSQL.

The problem is that you bind the query parameters with the wrong data types=
.
Don't use "setBigDecimal()", but "setLong()" if you want to bind a "bigint"=
.
An alternative is "setObject()" with "targetSqlType" set to "Types.BIGINT".

Yours,
Laurenz Albe