Re: sql statement not using all primary key values and poor performance

[James Pang <jamespang886@xxxxxxxxx>]

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 <jamespang886@xxxxxxxxx> 於 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