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.xx"
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_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
"idx_xx_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.XX WHERE ( ( XXXID =
$1 ) ) AND ( ( PARAMNAME = $2 ) ) AND ( ( SSSID = $3 ) )
Index Scan using pk_xx on test.xx (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: ((xx.paramname)::text = 'cdkkif'::text) <<<
just use only one key instead all primary keys.
Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND
((xx.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 於 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.xx"
> 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_xx" PRIMARY KEY, btree (xxxid, paramname, sssid)
> "idx_xx_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.XX WHERE ( ( XXXID =
> $1 ) ) AND ( ( PARAMNAME = $2 ) ) AND ( ( SSSID = $3 ) )
> Index Scan using pk_xx on test.xx (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: ((xx.paramname)::text = 'cdkkif'::text)
> <<< just use only one key instead all primary keys.
> Filter: (((xx.xxxid)::numeric = '18174044'::numeric) AND
> ((xx.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
>
