pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsql statement not using all primary key values and poor performance
5+ messages / 2 participants
[nested] [flat]
* sql statement not using all primary key values and poor performance
@ 2024-02-23 07:20 James Pang <jamespang886@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: James Pang @ 2024-02-23 07:20 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql statement not using all primary key values and poor performance
@ 2024-02-23 07:25 James Pang <jamespang886@gmail.com>
parent: James Pang <jamespang886@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: James Pang @ 2024-02-23 07:25 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql statement not using all primary key values and poor performance
@ 2024-02-23 09:17 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang <jamespang886@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-02-23 09:17 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql statement not using all primary key values and poor performance
@ 2024-02-23 10:21 James Pang <jamespang886@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: James Pang @ 2024-02-23 10:21 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: sql statement not using all primary key values and poor performance
@ 2024-02-23 11:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2024-02-23 11:48 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-02-23 11:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-23 07:20 sql statement not using all primary key values and poor performance James Pang <jamespang886@gmail.com>
2024-02-23 07:25 ` James Pang <jamespang886@gmail.com>
2024-02-23 09:17 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-23 10:21 ` James Pang <jamespang886@gmail.com>
2024-02-23 11:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox