Обсуждение: sql statement not using all primary key values and poor performance

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

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