Hi experts, 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 ? Thanks, James