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
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
