Hi experts, we have a SQL from Postgresql JDBC, primary key 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: xxxid, 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 ? Postgresql 14.10 version. how to avoid this conversion and make planner use all primary keys. Thanks, James James Pang <jamespang...@gmail.com> 於 2024年2月23日週五 下午3:20寫道: > 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 >