pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow performance with cursor
8+ messages / 4 participants
[nested] [flat]
* slow performance with cursor
@ 2021-06-25 16:09 Ayub Khan <ayub.hp@gmail.com>
0 siblings, 5 replies; 8+ messages in thread
From: Ayub Khan @ 2021-06-25 16:09 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
I am using postgresql 12 and using cursors in a stored procedure,
executing procedure which has cursor is slowing down the call. However if I
do not use the cursor and just execute the queries using JDBC (Java client)
it's fast.
Is there any setting which needs to be modified to improve the performance
of cursors. Also facing slow response with reading blobs (images) from db.
Not an ideal way for storing images in db but this is a legacy application
and wanted to check if there a quick tweak which can improve the
performance while reading blob data from db.
--Ayub
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-06-25 16:21 Justin Pryzby <pryzby@telsasoft.com>
parent: Ayub Khan <ayub.hp@gmail.com>
4 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2021-06-25 16:21 UTC (permalink / raw)
To: Ayub Khan <ayub.hp@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, Jun 25, 2021 at 07:09:31PM +0300, Ayub Khan wrote:
> I am using postgresql 12 and using cursors in a stored procedure,
> executing procedure which has cursor is slowing down the call. However if I
> do not use the cursor and just execute the queries using JDBC (Java client)
> it's fast.
Is the query slower, or is it slow to transfer tuples ?
I expect there would be a very high overhead if you read a large number of
tuples one at a time.
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
Is the slowness between the client-server or on the server side ?
Provide some details ?
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-06-25 16:37 Ayub Khan <ayub.hp@gmail.com>
parent: Ayub Khan <ayub.hp@gmail.com>
4 siblings, 0 replies; 8+ messages in thread
From: Ayub Khan @ 2021-06-25 16:37 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
slowness is on the database side as I see the CPU goes high for procedures
returning the result using cursors. If the same query is executed as a
prepared statement from Java client there is no slowness.
for example there are 84 rows returning all are text data from a query. If
the result is returned by cursor from the database, the cpu is high on the
db.
stored procedure A executes query Q and returns cursor1, this process has
high cpu on the database.
code changed in Java client to execute the same query as the prepared
statement and get back the resultset from the database, this does not
create a high cpu on the database.
--Ayub
On Fri, Jun 25, 2021 at 7:09 PM Ayub Khan <ayub.hp@gmail.com> wrote:
>
> I am using postgresql 12 and using cursors in a stored procedure,
> executing procedure which has cursor is slowing down the call. However if I
> do not use the cursor and just execute the queries using JDBC (Java client)
> it's fast.
>
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
>
> --Ayub
>
--
--------------------------------------------------------------------
Sun Certified Enterprise Architect 1.5
Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81
mobile:+966-502674604
----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude
will get you there. However, it's the Love
of God that will put you over the top!!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-06-25 18:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ayub Khan <ayub.hp@gmail.com>
4 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2021-06-25 18:04 UTC (permalink / raw)
To: Ayub Khan <ayub.hp@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Ayub Khan <ayub.hp@gmail.com> writes:
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
Possibly twiddling cursor_tuple_fraction would help. The default setting
tends to encourage fast-start plans, which might be counterproductive
if you're always fetching the entire result in one go.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-07-01 16:29 Ayub Khan <ayub.hp@gmail.com>
parent: Ayub Khan <ayub.hp@gmail.com>
4 siblings, 1 reply; 8+ messages in thread
From: Ayub Khan @ 2021-07-01 16:29 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
I set the cursor_tuple_fraction to 1 now I am seeing high cpu for fetach
all in
The number of rows returned is less than 200. Why is the high cpu being
shown for fetch all
-Ayub
On Fri, 25 Jun 2021, 19:09 Ayub Khan, <ayub.hp@gmail.com> wrote:
>
> I am using postgresql 12 and using cursors in a stored procedure,
> executing procedure which has cursor is slowing down the call. However if I
> do not use the cursor and just execute the queries using JDBC (Java client)
> it's fast.
>
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
>
> --Ayub
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-07-01 16:32 Justin Pryzby <pryzby@telsasoft.com>
parent: Ayub Khan <ayub.hp@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2021-07-01 16:32 UTC (permalink / raw)
To: Ayub Khan <ayub.hp@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, 25 Jun 2021, 19:09 Ayub Khan, <ayub.hp@gmail.com> wrote:
> I am using postgresql 12 and using cursors in a stored procedure,
> executing procedure which has cursor is slowing down the call. However if I
> do not use the cursor and just execute the queries using JDBC (Java client)
> it's fast.
>
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
On Thu, Jul 01, 2021 at 07:29:31PM +0300, Ayub Khan wrote:
> I set the cursor_tuple_fraction to 1 now I am seeing high cpu for fetach
> all in
>
> The number of rows returned is less than 200. Why is the high cpu being
> shown for fetch all
It seems like you're asking for help, but need to show the stored procedure
you're asking for help with.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-07-01 20:25 Ayub Khan <ayub.hp@gmail.com>
parent: Ayub Khan <ayub.hp@gmail.com>
4 siblings, 1 reply; 8+ messages in thread
From: Ayub Khan @ 2021-07-01 20:25 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Justin,
Below is the stored procedure, is there any scope for improvement?
CREATE OR REPLACE PROCEDURE "new_api_pkg$get_menu_details_p"(
i_user_id bigint,
i_menu_item_id bigint,
INOUT o_menu refcursor,
INOUT o_item refcursor,
INOUT o_choice refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
IF i_user_id IS NOT NULL THEN
OPEN o_menu FOR
SELECT
mi.menu_item_id, mi.menu_item_name, mi.menu_item_title,
mi.restaurant_id, case when mi.image !=null then 'Y' when mi.image is null
then 'N' end as has_image,
0.0 AS rating, 0 AS votes, 0 AS own_rating
FROM menu_item AS mi
WHERE mi.menu_item_id = i_menu_item_id AND mi.active = 'Y';
ELSE
OPEN o_menu FOR
SELECT mi.menu_item_id, mi.menu_item_name, mi.menu_item_title,
mi.restaurant_id, case when mi.image !=null then 'Y' when mi.image is null
then 'N' end as has_image,
0.0 AS rating, 0 AS votes, 0 AS own_rating
FROM menu_item AS mi
WHERE mi.menu_item_id = i_menu_item_id AND mi.active = 'Y';
END IF;
OPEN o_item FOR
SELECT
c.menu_item_variant_id, c.menu_item_variant_type_id,
c.package_type_code, c.packages_only, c.price,
CASE
WHEN c.package_type_code = 'P' THEN
(SELECT SUM(miv1.calories) FROM package_component AS
pkg_cpm1
INNER JOIN menu_item_variant AS miv1 ON
pkg_cpm1.component_id = miv1.menu_item_variant_id WHERE pkg_cpm1.package_id
= c.menu_item_variant_id)
ELSE c.calories
END AS calories, c.size_id, c.parent_menu_item_variant_id,
d.menu_item_variant_type_desc, d.menu_item_variant_type_desc_ar,
e.size_desc, e.size_desc_ar,15 AS preparation_time,
(SELECT STRING_AGG(CONCAT_WS('', mi.menu_item_name, ' ',
s.size_desc), ' + '::TEXT ORDER BY pc.component_id)
FROM package_component AS pc, menu_item_variant AS miv,
menu_item AS mi, menu_item_variant_type AS mivt, item_size AS s
WHERE pc.component_id = miv.menu_item_variant_id AND
miv.menu_item_id = mi.menu_item_id AND miv.size_id = s.size_id
AND pc.package_id = c.menu_item_variant_id AND mivt.is_hidden
= 'false' AND mivt.menu_item_variant_type_id = miv.menu_item_variant_type_id
GROUP BY pc.package_id) AS package_name
FROM menu_item AS a, menu_item_variant AS c, menu_item_variant_type
AS d, item_size AS e
WHERE a.menu_item_id = c.menu_item_id AND
c.menu_item_variant_type_id = d.menu_item_variant_type_id AND d.is_hidden =
'false'
AND c.size_id = e.size_id AND a.menu_item_id = i_menu_item_id AND
a.active = 'Y' AND c.deleted = 'N'
ORDER BY c.menu_item_variant_id;
OPEN o_choice FOR
SELECT
c.choice_id, c.choice_name, c.choice_name_ar, c.calories
FROM choice AS c, menu_item_choice AS mc, menu_item AS mi
WHERE c.choice_id = mc.choice_id AND mc.menu_item_id =
mi.menu_item_id AND mc.menu_item_id = i_menu_item_id AND mi.active = 'Y';
END;
$BODY$;
On Fri, Jun 25, 2021 at 7:09 PM Ayub Khan <ayub.hp@gmail.com> wrote:
>
> I am using postgresql 12 and using cursors in a stored procedure,
> executing procedure which has cursor is slowing down the call. However if I
> do not use the cursor and just execute the queries using JDBC (Java client)
> it's fast.
>
> Is there any setting which needs to be modified to improve the performance
> of cursors. Also facing slow response with reading blobs (images) from db.
> Not an ideal way for storing images in db but this is a legacy application
> and wanted to check if there a quick tweak which can improve the
> performance while reading blob data from db.
>
> --Ayub
>
--
--------------------------------------------------------------------
Sun Certified Enterprise Architect 1.5
Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81
mobile:+966-502674604
----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude
will get you there. However, it's the Love
of God that will put you over the top!!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: slow performance with cursor
@ 2021-07-02 14:30 Tomas Vondra <tomas.vondra@enterprisedb.com>
parent: Ayub Khan <ayub.hp@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tomas Vondra @ 2021-07-02 14:30 UTC (permalink / raw)
To: Ayub Khan <ayub.hp@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On 7/1/21 10:25 PM, Ayub Khan wrote:
> Justin,
>
> Below is the stored procedure, is there any scope for improvement?
>
Hard to say, based on just the stored procedure source code. The queries
are not too complex, but we don't know which of them gets selected for
each cursor, and which of them is the slow one.
I suggest you identify which of the cursors is the most problematic one,
and focus on investigating it alone. Show us the explain analyze for
that query with different cursor_tuple_fraction values and without the
cursort, and so on.
As Tom said, for a cursor the optimizer may be picking a plan with low
startup cost, on the basis that that's good for a cursor. But if you're
always consuming all the tuples, that may be inefficient. It's often an
issue for queries with LIMIT, but none of the queries you include that
clause, so who knows ...
Try identifying which of the cursors is causing the issues, show us the
explain analyze for that query (with and without the cursor), and that
should tell us more.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-07-02 14:30 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-25 16:09 slow performance with cursor Ayub Khan <ayub.hp@gmail.com>
2021-06-25 16:21 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-25 16:37 ` Ayub Khan <ayub.hp@gmail.com>
2021-06-25 18:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-07-01 16:29 ` Ayub Khan <ayub.hp@gmail.com>
2021-07-01 16:32 ` Justin Pryzby <pryzby@telsasoft.com>
2021-07-01 20:25 ` Ayub Khan <ayub.hp@gmail.com>
2021-07-02 14:30 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
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