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

sql statement not using all primary key values and poor performance

From

James Pang

Date:

23 February 2024, 07:20:40

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

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

From

James Pang

Date:

23 February 2024, 07:25:36

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

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

From

Laurenz Albe

Date:

23 February 2024, 09:17:34

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.XXXXXXWHERE  ( ( 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.011rows=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

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

From

James Pang

Date:

23 February 2024, 10:21:05

it's a third-party vendor application, not easy to change their code. is it possible to 1) in Postgresql JDBC driver connection, set plan_cache_mode=force_custom_plan or 2) some other parameters can workaround this issue?

Thanks,

James

Laurenz Albe <laurenz.albe@cybertec.at> 於 2024年2月23日週五下午5:17寫道：

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

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

From

Laurenz Albe

Date:

23 February 2024, 11:48:55

On Fri, 2024-02-23 at 18:21 +0800, James Pang wrote:
> it's a third-party vendor application, not easy to change their code.

Then the application is broken, and you should make the vendor fix it.

> is it possible to   1) in Postgresql JDBC driver connection, set
> plan_cache_mode=force_custom_plan    or 2) some other parameters can workaround this issue?

You can set "prepareThreshold" to 0 to keep the JDBC driver from using
prepared statements in PostgreSQL.  I am not sure if that is enough to
fix the problem.

Yours,
Laurenz Albe