it's a third-party vendor application, not easy to change their code. is it possible to 1) in Postgresql JDBC driver connection, set plan_cache_mode=force_custom_plan or 2) some other parameters can workaround this issue?
Thanks, James Laurenz Albe <laurenz.a...@cybertec.at> 於 2024年2月23日週五 下午5:17寫道： > On Fri, 2024-02-23 at 15:20 +0800, James Pang wrote: > > we have a SQL from Postgresql JDBC, primary is based on > (bigint,varchar2,bigint), > > but from sql plan, it convert to ::numeric so the plan just use one > "varchar" > > key column and use the other 2 bigint keys as filters. what's the cause > about that ? > > > > Table "test.xxxxxx" > > Column | Type | Collation | > Nullable | Default > > > ------------------+--------------------------------+-----------+----------+--------- > > xxxid | bigint | | not > null | > > paramname | character varying(512) | | not > null | > > paramvalue | character varying(1536) | | > | > > sssid | bigint | | not > null | > > createtime | timestamp(0) without time zone | | > | > > lastmodifiedtime | timestamp(0) without time zone | | > | > > mmmuuid | character varying(32) | | > | > > Indexes: > > "pk_xxxxxx" PRIMARY KEY, btree (xxxid, paramname, sssid) > > "idx_xxxxxx_mmmuuid" btree (sssid, mmmuuid, paramname) > > > > SET extra_float_digits = 3 > > > > duration: 7086.014 ms plan: > > Query Text: SELECT XXXFID, PARAMNAME, PARAMVALUE, SSSID, > CREATETIME, LASTMODIFIEDTIME, MMMUUID FROM test.XXXXXX WHERE ( ( XXXID = > $1 ) ) AND ( ( PARAMNAME = $2 ) ) AND ( ( SSSID = $3 ) ) > > Index Scan using pk_xxxxxx on test.xxxxxx > (cost=0.57..2065259.09 rows=1 width=86) (actual time=7086.010..7086.011 > rows=0 loops=1) > > Output: confid, paramname, paramvalue, sssid, createtime, > lastmodifiedtime, mmmuuid > > Index Cond: ((xxxxxx.paramname)::text = 'cdkkifffff'::text) > <<< just use only one key instead all primary keys. > > Filter: (((xxxxxx.xxxid)::numeric = '18174044'::numeric) AND > ((xxxxxx.sssid)::numeric = '253352'::numeric)) <<< it's bigint but > converted to numeric > > Buffers: shared read=1063470 > > I/O Timings: read=4402.029 > > > > it's from JDBC, we saw this JDBC driver try to set extra_float_digits = > 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 > to > not lose precision with "real" and "double precision" values on old > versions > 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 >
