pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance tuning in large function / transaction
23+ messages / 6 participants
[nested] [flat]
* performance tuning in large function / transaction
@ 2001-12-13 16:27 MindTerm <mindterm@yahoo.com>
2001-12-13 18:56 ` Re: performance tuning in large function / transaction Jason Earl <jason.earl@simplot.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:09 ` Re: performance tuning in large function / transaction Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 3 replies; 23+ messages in thread
From: MindTerm @ 2001-12-13 16:27 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I am writing a large function which perform more
than 2000 modify statements ( delete / insert ) . The
execution time of this function is not acceptable. How
can I config postgresql ( in postgresql.conf ?? ) so
that the performance will be better ? The machine
runing postgresql have spare memory and harddisk
space.
M.T.
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-13 18:56 ` Jason Earl <jason.earl@simplot.com>
2 siblings, 0 replies; 23+ messages in thread
From: Jason Earl @ 2001-12-13 18:56 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
Start here:
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html
If you have already fiddled around with these settings then share some
of the queries (and their plans).
Jason
MindTerm <mindterm@yahoo.com> writes:
> Hi all,
>
> I am writing a large function which perform more
> than 2000 modify statements ( delete / insert ) . The
> execution time of this function is not acceptable. How
> can I config postgresql ( in postgresql.conf ?? ) so
> that the performance will be better ? The machine
> runing postgresql have spare memory and harddisk
> space.
>
>
> M.T.
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-13 19:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2001-12-13 19:17 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Thu, 13 Dec 2001, MindTerm wrote:
> Hi all,
>
> I am writing a large function which perform more
> than 2000 modify statements ( delete / insert ) . The
> execution time of this function is not acceptable. How
> can I config postgresql ( in postgresql.conf ?? ) so
> that the performance will be better ? The machine
> runing postgresql have spare memory and harddisk
> space.
It's hard without more information, is the function like
an internal function or do you mean an external one?
In the latter case, the first thing to do is make
sure you're not in autocommit (ie, you've begun a
transaction) since otherwise you're doing 2000
separate transactions.
The settings that are the first to look at in the
postgresql.conf are shared_buffers and sort_mem.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-14 02:46 ` MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 23+ messages in thread
From: MindTerm @ 2001-12-14 02:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi,
I am writing a function (plpgsql) which equals to a
single transaction transaction in postgresql ( as I
known ). So I think that it is not a autocommmit mode.
I have add following lines in postgresql.conf.
postgresql.conf:
====================
shared_buffers = 640
wal_buffers = 80
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Thu, 13 Dec 2001, MindTerm wrote:
>
> > Hi all,
> >
> > I am writing a large function which perform more
> > than 2000 modify statements ( delete / insert ) .
> The
> > execution time of this function is not acceptable.
> How
> > can I config postgresql ( in postgresql.conf ?? )
> so
> > that the performance will be better ? The machine
> > runing postgresql have spare memory and harddisk
> > space.
>
> It's hard without more information, is the function
> like
> an internal function or do you mean an external one?
> In the latter case, the first thing to do is make
> sure you're not in autocommit (ie, you've begun a
> transaction) since otherwise you're doing 2000
> separate transactions.
> The settings that are the first to look at in the
> postgresql.conf are shared_buffers and sort_mem.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-14 03:02 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:43 ` Re: performance tuning in large function / transaction Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 23+ messages in thread
From: Stephan Szabo @ 2001-12-14 03:02 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Thu, 13 Dec 2001, MindTerm wrote:
> I am writing a function (plpgsql) which equals to a
> single transaction transaction in postgresql ( as I
> known ). So I think that it is not a autocommmit mode.
>
> I have add following lines in postgresql.conf.
>
> postgresql.conf:
> ====================
> shared_buffers = 640
> wal_buffers = 80
It depends on how much memory you have, but even 640 is pretty
low (I think that works out to 5M). Probably a few thousand
is better if you've got the ram.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-14 03:34 ` MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 06:47 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 2 replies; 23+ messages in thread
From: MindTerm @ 2001-12-14 03:34 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi all,
I will try to assign more memory to shared memory,
say 50M ~ 100M .
I am runing something like that ..
cursor loop 1
cursor loop 2
cursor loop 3
tmp = primary key
delete tmp ....
insert tmp ....
end loop 3
end loop 2
end loop 3
will delete and insert record with same primary key
within a transaction reduce the performance ?
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Thu, 13 Dec 2001, MindTerm wrote:
>
> > I am writing a function (plpgsql) which equals
> to a
> > single transaction transaction in postgresql ( as
> I
> > known ). So I think that it is not a autocommmit
> mode.
> >
> > I have add following lines in postgresql.conf.
> >
> > postgresql.conf:
> > ====================
> > shared_buffers = 640
> > wal_buffers = 80
>
> It depends on how much memory you have, but even 640
> is pretty
> low (I think that works out to 5M). Probably a few
> thousand
> is better if you've got the ram.
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-14 06:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
1 sibling, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2001-12-14 06:42 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Thu, 13 Dec 2001, MindTerm wrote:
> Hi all,
>
> I will try to assign more memory to shared memory,
> say 50M ~ 100M .
>
> I am runing something like that ..
>
> cursor loop 1
> cursor loop 2
> cursor loop 3
> tmp = primary key
> delete tmp ....
> insert tmp ....
> end loop 3
> end loop 2
> end loop 3
>
> will delete and insert record with same primary key
> within a transaction reduce the performance ?
Hmm, that might, is the tmp different for each
combination of 1,2 and 3? I'm not really sure.
You might also want to watch the backend's memory
usage during the call to see how big it gets just
to make sure there's not something bad happening.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-14 08:05 ` MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 23+ messages in thread
From: MindTerm @ 2001-12-14 08:05 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi all,
senario:
cursor 1 loop ( e.g. find student id )
cursor 2 loop ( e.g. find courses of this student )
cursor 3 loop ( e.g. update course information )
delele course detail ...
or
delete course detail ... ( same primary key )
insert course detail ... ( same primary key )
end loop 3
end loop 2
end loop 1
It did 75 delete actions 140 update actions ( delete
and insert ). The process time was about 5-6 minutes
while oracle was 10 seconds to 20 seconds .
postgresql.conf :
=================
shared_buffers = 3200
wal_buffers = 80
running on linux 7.1 , 512M ram.
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Thu, 13 Dec 2001, MindTerm wrote:
>
> > Hi all,
> >
> > I will try to assign more memory to shared
> memory,
> > say 50M ~ 100M .
> >
> > I am runing something like that ..
> >
> > cursor loop 1
> > cursor loop 2
> > cursor loop 3
> > tmp = primary key
> > delete tmp ....
> > insert tmp ....
> > end loop 3
> > end loop 2
> > end loop 3
> >
> > will delete and insert record with same primary
> key
> > within a transaction reduce the performance ?
>
> Hmm, that might, is the tmp different for each
> combination of 1,2 and 3? I'm not really sure.
> You might also want to watch the backend's memory
> usage during the call to see how big it gets just
> to make sure there's not something bad happening.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-14 08:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2001-12-14 08:40 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Fri, 14 Dec 2001, MindTerm wrote:
> Hi all,
>
> senario:
>
> cursor 1 loop ( e.g. find student id )
> cursor 2 loop ( e.g. find courses of this student )
> cursor 3 loop ( e.g. update course information )
> delele course detail ...
> or
> delete course detail ... ( same primary key )
> insert course detail ... ( same primary key )
> end loop 3
> end loop 2
> end loop 1
>
> It did 75 delete actions 140 update actions ( delete
> and insert ). The process time was about 5-6 minutes
> while oracle was 10 seconds to 20 seconds .
Can you send the actual function and table schema for the associated
tables and sizes of the tables involved (I figure the data itself is
probably not available, but we may be able to see what's going on then)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-14 09:06 ` MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 23+ messages in thread
From: MindTerm @ 2001-12-14 09:06 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi all,
table :
application : 220 rows ;
usermenu : 6055 rows ;
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Fri, 14 Dec 2001, MindTerm wrote:
>
> > Hi all,
> >
> > senario:
> >
> > cursor 1 loop ( e.g. find student id )
> > cursor 2 loop ( e.g. find courses of this
> student )
> > cursor 3 loop ( e.g. update course information
> )
> > delele course detail ...
> > or
> > delete course detail ... ( same primary key
> )
> > insert course detail ... ( same primary key
> )
> > end loop 3
> > end loop 2
> > end loop 1
> >
> > It did 75 delete actions 140 update actions (
> delete
> > and insert ). The process time was about 5-6
> minutes
> > while oracle was 10 seconds to 20 seconds .
>
> Can you send the actual function and table schema
> for the associated
> tables and sizes of the tables involved (I figure
> the data itself is
> probably not available, but we may be able to see
> what's going on then)
>
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
Attachments:
xxx (text/plain, 7.5K) download
-- Table: usermenu
CREATE TABLE "usermenu" (
"um_user" numeric(11, 0) NOT NULL,
"um_app_id" numeric(11, 0) NOT NULL,
"um_order" varchar(50),
"um_gif" varchar(100),
"um_alt" varchar(50),
"um_link" varchar(100),
"um_level" varchar(5),
"um_initial" varchar(1),
"um_gallery_id" numeric(11, 0),
"um_en_length" numeric(4, 0),
"um_tc_length" numeric(4, 0),
"um_sc_length" numeric(4, 0),
"um_terminator" varchar(1),
"um_menu" varchar(1),
"um_en_name" varchar(1000),
"um_tc_name" varchar(1000),
"um_sc_name" varchar(1000),
"um_ext" varchar(1),
CONSTRAINT "usermenu_pkey" PRIMARY KEY ("um_user", "um_app_id"),
CONSTRAINT "usermenu_fk1992931308295" FOREIGN KEY (um_app_id) REFERENCES "application" (app_id)
);
-- Table: application
CREATE TABLE "application" (
"app_id" numeric(11, 0) NOT NULL,
"app_en_name" varchar(100) NOT NULL,
"app_tc_name" varchar(100),
"app_sc_name" varchar(100),
"app_menu" varchar(1),
"app_installed" varchar(1),
"app_order" varchar(50),
"app_alt" varchar(50),
"app_gif" varchar(100),
"app_link" varchar(100),
"app_initial" varchar(1),
"app_gallery_id" numeric(11, 0),
"app_terminator" varchar(1),
"app_en_length" numeric(4, 0),
"app_tc_length" numeric(4, 0),
"app_sc_length" numeric(4, 0),
"app_ext" varchar(1),
"app_type" varchar(30),
CONSTRAINT "application_pkey" PRIMARY KEY ("app_id")
);
BEGIN TRANSACTION;
drop function hku_usermenu( numeric, varchar, int, varchar );
CREATE FUNCTION hku_USERMENU ( numeric, varchar , int , varchar ) RETURNS varchar AS '
DECLARE
vGroupId ALIAS FOR $1 ;
vUserID ALIAS FOR $2 ;
vGalleryId ALIAS FOR $3 ;
rSuccess ALIAS FOR $4 ;
vTmpUserID user_group.ug_user_id%TYPE;
vTmpGroupID user_group.ug_id%TYPE;
vTmpOrder application.app_order%TYPE;
vAclLevel acl.acl_level%TYPE;
vAppId application.app_id%TYPE;
vAppMenu application.app_menu%TYPE;
vAppInstalled application.app_installed%TYPE;
vAppTerminator application.app_terminator%TYPE;
vAppOrder application.app_order%TYPE;
vAppOrderLen int:=0;
vLength int:=0;
vOrder application.app_order%TYPE;
app_rec application%ROWTYPE;
cnt1 int:=0;
cnt2 int:=0;
vcnt varchar(10):='''';
v_str varchar(1500):='''';
c1 record ;
c2 record ;
tmptime timestamp;
t1 int := 0 ;
t2 int := 0 ;
BEGIN
select timeofday() into tmptime ;
RAISE NOTICE ''[MT][START]HKU_USERMENU % % % %'', vGroupId, vUserID, vGalleryId, tmptime ;
if vUserID = '' '' or vUserID = '''' or vUserID is null then
vTmpGroupID := vGroupId;
else
vTmpGroupID := 0;
end if;
for c1 in
select user_id from (
(SELECT UG_USER_ID as user_id
FROM USER_GROUP
WHERE UG_ID = vTmpGroupID
and GALLERY_ID = vGalleryId)
union
(SELECT USER_ID as user_id
FROM HKU_USERS
WHERE text( USER_ID ) = vUserId
and GALLERY_ID = vGalleryId)
) tmp
loop
vTmpUserID := text( c1.user_id );
delete from usermenu where um_user = vTmpUserID ;
for c2 in
select ACL_APP, max(ACL_LEVEL) as acl_level, app.APP_ORDER, length(app.APP_ORDER) as app_order_length, app.APP_MENU, app.app_installed, app.app_terminator
from ACL, APPLICATION app
where ACL_APP = app.APP_ID
and ACL_GALLERY_ID = vGalleryId
and app.APP_GALLERY_ID = vGalleryId
and ACL_GRP in
(select u.ug_id
from user_group u, user_group_master ug
where u.ug_user_id = vTmpUserID
and ug.ug_id = u.ug_id
and (ug.deleted = ''N'' or ug.deleted IS NULL )
and u.gallery_id = vGalleryID
and ug.gallery_id = vGalleryID
)
group by acl_app, app.app_order,app.APP_MENU, app.app_installed, app.app_terminator
order by app.app_order desc, ACL_LEVEL
loop
vAppId := c2.ACL_APP ;
vAclLevel := c2.acl_level ;
vAppOrder := c2.APP_ORDER ;
vAppOrderLen := c2.app_order_length ;
vAppMenu := c2.APP_MENU ;
vAppInstalled := c2.app_installed ;
vAppTerminator := c2.app_terminator ;
vcnt := 0;
vLength := vAppOrderLen;
vTmpOrder := vAppOrder;
select count(*) into vcnt
from ACL, APPLICATION app
where ACL_APP = app.app_id
and ACL_LEVEL > 0
and APP_INSTALLED = ''Y''
and ACL_GALLERY_ID = vGalleryID
and app.APP_GALLERY_ID = vGalleryID
and substr(app.app_order,1,vAppOrderLen) = vAppOrder
and app.app_order <> vAppOrder
and ACL_GRP in
(select u.ug_id
from user_group u, user_group_master ug
where u.ug_user_id = vTmpUserID
and ug.ug_id = u.ug_id
and (ug.deleted = ''N'' or ug.deleted IS NULL)
and u.gallery_id = vGalleryID
and ug.gallery_id = vGalleryID);
IF vAclLevel IS NULL or vAclLevel <= 0 or upper(vAppInstalled) = ''N'' THEN
delete from usermenu where um_user = vTmpUserID and substr( um_order, 1, vAppOrderLen ) = vAppOrder;
t1 = t1 + 1 ;
ELSE IF vcnt > 0 or vAppTerminator = ''Y'' THEN
for app_rec in SELECT * FROM APPLICATION WHERE app_order = vTmpOrder and app_gallery_id = vGalleryId
loop
delete from usermenu where um_user = vTmpUserID and um_app_id = app_rec.app_id;
insert into usermenu(um_user, um_app_id, um_order, um_level, um_menu, um_gallery_id,
um_gif, um_alt, um_link, um_terminator,
um_en_name, um_tc_name, um_sc_name, um_ext)
values (vTmpUserID, app_rec.app_id, app_rec.app_order, text( vAclLevel ) , app_rec.app_menu, vGalleryId,
app_rec.app_gif, app_rec.app_alt, app_rec.app_link, app_rec.app_terminator,
app_rec.app_en_name, app_rec.app_tc_name, app_rec.app_sc_name, app_rec.app_ext);
/*RAISE NOTICE ''insert into usermenu(um_user, um_app_id, um_order, um_level, um_menu, um_gallery_id,
um_gif, um_alt, um_link, um_terminator,
um_en_name, um_tc_name, um_sc_name, um_ext)
values ( %, % , ''''%'''', % , ''''%'''', % , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' ); ''
,
vTmpUserID, app_rec.app_id, app_rec.app_order, vAclLevel , app_rec.app_menu, vGalleryId,
app_rec.app_gif, app_rec.app_alt, app_rec.app_link, app_rec.app_terminator,
app_rec.app_en_name, app_rec.app_tc_name, app_rec.app_sc_name, app_rec.app_ext ; */
t2 = t2 + 1 ;
/* SELECT count(*) into rSuccess from usermenu WHERE um_user = vTmpUserID and um_app_id = app_rec.app_id; */
EXIT; /* run once (if c3%FOUND then) */
end loop;
END IF;
END IF;
vcnt := 0;
end loop;
end loop;
select timeofday() into tmptime ;
RAISE NOTICE ''[MT][STOP]HKU_USERMENU % % % % % %'', vGroupId, vUserID, vGalleryId, tmptime, t1, t2 ;
return ''Y'' ;
END;
' LANGUAGE 'plpgsql' ;
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-14 14:44 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2001-12-14 14:44 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Fri, 14 Dec 2001, MindTerm wrote:
> Hi all,
>
> table :
> application : 220 rows ;
> usermenu : 6055 rows ;
Well, I'd need the other tables involved in the function
too, but to start with, try rewriting the queries in the
function to use EXISTS rather than IN (see the faq), or
bring them into a normal join with the other tables where
possible.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-17 02:58 ` MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 23+ messages in thread
From: MindTerm @ 2001-12-17 02:58 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Dear Stephan,
-- Table: user_group
CREATE TABLE "user_group" (
"ug_id" numeric(10, 0),
"ug_user_id" numeric(11, 0),
"gallery_id" numeric(11, 0),
"deleted" varchar(1) DEFAULT 'N',
CONSTRAINT "ug_mid" FOREIGN KEY (ug_id) REFERENCES
"user_group_master" (ug_id) ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "user_group_fk" FOREIGN KEY (ug_user_id)
REFERENCES "i2users" (user_id) ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
-- Table: usermenu
CREATE TABLE "usermenu" (
"um_user" numeric(11, 0) NOT NULL,
"um_app_id" numeric(11, 0) NOT NULL,
"um_order" varchar(50),
"um_gif" varchar(100),
"um_alt" varchar(50),
"um_link" varchar(100),
"um_level" varchar(5),
"um_initial" varchar(1),
"um_gallery_id" numeric(11, 0),
"um_en_length" numeric(4, 0),
"um_tc_length" numeric(4, 0),
"um_sc_length" numeric(4, 0),
"um_terminator" varchar(1),
"um_menu" varchar(1),
"um_en_name" varchar(1000),
"um_tc_name" varchar(1000),
"um_sc_name" varchar(1000),
"um_ext" varchar(1),
CONSTRAINT "usermenu_pkey" PRIMARY KEY ("um_user",
"um_app_id"),
CONSTRAINT "usermenu_fk1992931308295" FOREIGN KEY
(um_app_id) REFERENCES "application" (app_id) ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
INITIALLY IMMEDIATE
);
-- Table: application
CREATE TABLE "application" (
"app_id" numeric(11, 0) NOT NULL,
"app_en_name" varchar(100) NOT NULL,
"app_tc_name" varchar(100),
"app_sc_name" varchar(100),
"app_menu" varchar(1),
"app_installed" varchar(1),
"app_order" varchar(50),
"app_alt" varchar(50),
"app_gif" varchar(100),
"app_link" varchar(100),
"app_initial" varchar(1),
"app_gallery_id" numeric(11, 0),
"app_terminator" varchar(1),
"app_en_length" numeric(4, 0),
"app_tc_length" numeric(4, 0),
"app_sc_length" numeric(4, 0),
"app_ext" varchar(1),
"app_type" varchar(30),
CONSTRAINT "application_pkey" PRIMARY KEY ("app_id")
);
-- Table: acl
CREATE TABLE "acl" (
"acl_id" numeric(10, 0) DEFAULT 0 NOT NULL,
"acl_app" numeric(10, 0),
"acl_grp" numeric(10, 0),
"acl_level" numeric(3, 0),
"acl_gallery_id" numeric(11, 0),
CONSTRAINT "acl_acl_app_key" UNIQUE ("acl_app",
"acl_grp"),
CONSTRAINT "acl_pkey" PRIMARY KEY ("acl_id"),
CONSTRAINT "acl_fk9992931283980" FOREIGN KEY
(acl_app) REFERENCES "application" (app_id) ON DELETE
NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY
IMMEDIATE,
CONSTRAINT "acl_ug" FOREIGN KEY (acl_grp) REFERENCES
"user_group_master" (ug_id) ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
-- Table: i2users
CREATE TABLE "i2users" (
"user_id" numeric(11, 0) NOT NULL,
"parent_id" numeric(11, 0) NOT NULL,
"password" varchar(128) NOT NULL,
"status" varchar(1),
"tx_password" varchar(125),
"login_name" varchar(125),
"join_date" timestamp,
"deleted_date_time" timestamp,
"sc_title" varchar(50),
"sc_department" varchar(50),
"en_title" varchar(50),
"en_department" varchar(50),
"gallery_id" numeric(11, 0),
"tc_title" varchar(50),
"tc_department" varchar(50),
"reject_reason" varchar(500),
"approver" numeric(11, 0),
"report_to" varchar(50),
"the_level" numeric(11, 0),
"modified_date" timestamp DEFAULT 'now',
"modified_password" timestamp DEFAULT 'now',
"max_prospect" numeric(11, 0),
"ratio" numeric(11, 0),
"location" varchar(20),
"nickname" varchar(50),
"team" varchar(255),
"email_address" varchar(125),
"mobile" varchar(20),
"en_name" varchar(100),
"tc_name" varchar(100),
"sc_name" varchar(100),
"approve_limit" numeric(14, 4),
"default_language" numeric(1, 0),
CONSTRAINT "i2users_pkey" PRIMARY KEY ("user_id"),
CONSTRAINT "i2users_fk2995438601367" FOREIGN KEY
(parent_id) REFERENCES "i2company" (company_id) ON
DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
INITIALLY IMMEDIATE
);
-- Table: user_group_master
CREATE TABLE "user_group_master" (
"ug_id" numeric(11, 0) NOT NULL,
"ug_en_name" varchar(50),
"ug_tc_name" varchar(50),
"ug_sc_name" varchar(50),
"gallery_id" numeric(11, 0),
"deleted" varchar(1) DEFAULT 'N',
"code" varchar(10),
"company_id" numeric(11, 0) NOT NULL,
"ug_en_description" varchar(1000),
"ug_tc_description" varchar(1000),
"ug_sc_description" varchar(1000),
CONSTRAINT "user_group_maste_gallery_id_key" UNIQUE
("gallery_id", "code", "company_id"),
CONSTRAINT "user_group_master_pkey" PRIMARY KEY
("ug_id"),
CONSTRAINT "user_group_ma_fk2996119764375" FOREIGN
KEY (company_id) REFERENCES "i2company" (company_id)
ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
INITIALLY IMMEDIATE
);
all tables here
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
>
> On Fri, 14 Dec 2001, MindTerm wrote:
>
> > Hi all,
> >
> > table :
> > application : 220 rows ;
> > usermenu : 6055 rows ;
>
> Well, I'd need the other tables involved in the
> function
> too, but to start with, try rewriting the queries in
> the
> function to use EXISTS rather than IN (see the faq),
> or
> bring them into a normal join with the other tables
> where
> possible.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-17 03:41 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 04:06 ` Re: performance tuning in large function / transaction Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-18 07:39 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
0 siblings, 2 replies; 23+ messages in thread
From: Stephan Szabo @ 2001-12-17 03:41 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
> --- Stephan Szabo <sszabo@megazone23.bigpanda.com>
> wrote:
> >
> > On Fri, 14 Dec 2001, MindTerm wrote:
> >
> > > Hi all,
> > >
> > > table :
> > > application : 220 rows ;
> > > usermenu : 6055 rows ;
> >
> > Well, I'd need the other tables involved in the
> > function
> > too, but to start with, try rewriting the queries in
> > the
> > function to use EXISTS rather than IN (see the faq),
> > or
> > bring them into a normal join with the other tables
> > where
> > possible.
As a note, does rewriting the queries in the function
to use exists rather than in help any?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-17 04:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-17 05:39 ` Re: performance tuning in large function / transaction Bruce Momjian <pgman@candle.pha.pa.us>
2001-12-17 06:21 ` Re: performance tuning in large function / transaction Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 23+ messages in thread
From: Christopher Kings-Lynne @ 2001-12-17 04:06 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
> As a note, does rewriting the queries in the function
> to use exists rather than in help any?
Is it true that the IN command is implemented sort of as a linked list
linear time search? Is there any plan for a super-fast implementation of
'IN'?
Chris
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 04:06 ` Re: performance tuning in large function / transaction Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2001-12-17 05:39 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Bruce Momjian @ 2001-12-17 05:39 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; MindTerm <mindterm@yahoo.com>; pgsql-sql
> > As a note, does rewriting the queries in the function
> > to use exists rather than in help any?
>
> Is it true that the IN command is implemented sort of as a linked list
> linear time search? Is there any plan for a super-fast implementation of
> 'IN'?
I keep asking for it and am told it is hard to do.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 04:06 ` Re: performance tuning in large function / transaction Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2001-12-17 06:21 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Tom Lane @ 2001-12-17 06:21 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; MindTerm <mindterm@yahoo.com>; pgsql-sql
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Is it true that the IN command is implemented sort of as a linked list
> linear time search? Is there any plan for a super-fast implementation of
> 'IN'?
This deserves a somewhat long-winded answer.
Postgres presently supports two kinds of IN (I'm not sure whether SQL92
allows any additional kinds):
1. Scalar-list IN: foo IN ('bar', 'baz', 'quux', ...)
2. Sub-select IN: foo IN (SELECT bar FROM ...)
In the scalar-list form, a variable is compared to an explicit list of
constants or expressions. This form is exactly equivalent to
foo = 'bar' OR foo = 'baz' OR foo = 'quux' OR ...
and is converted into that form by the parser. The planner is capable
of converting a WHERE clause of this kind into multiple passes of
indexscan, when foo is an indexed column and all the IN-list elements
are constants. Whether it actually will make that conversion depends
on the usual vagaries of pg_statistic entries, etc. But if it's a
unique or fairly-selective index, and there aren't a huge number of
entries in the IN list, a multiple indexscan should be a good plan.
In the sub-select form, we pretty much suck: for each tuple in the outer
query, we run the inner query until we find a matching value or the
inner query ends. This is basically a nested-loop scenario, with the
only (minimally) redeeming social value being that the planner realizes
it should pick a fast-start plan for the inner query. I think it should
be possible to convert this form into a modified kind of join (sort of
the reverse of an outer join: rather than at least one result per
lefthand row, at most one result per lefthand row), and then we could
use join methods that are more efficient than nested-loop. But no one's
tried to make that happen yet.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-18 07:39 ` MindTerm <mindterm@yahoo.com>
2001-12-18 07:50 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 1 reply; 23+ messages in thread
From: MindTerm @ 2001-12-18 07:39 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Dear stephan,
I found that the long execution time was due to
following statement which will execute many times in a
loop:
select count(*)
from ACL, APPLICATION app
where ACL_APP = app.app_id
and APP_INSTALLED = 'Y'
and ACL_LEVEL > 0
and ACL_GALLERY_ID = 1
and app.APP_GALLERY_ID = 1
and substr(app.app_order,1, 6 ) = '021101'
and app.app_order <> '021101'
and ACL_GRP in
(select u.ug_id
from user_group u, user_group_master
ug
where u.ug_user_id = 5170
and ug.ug_id = u.ug_id
and (ug.deleted = 'N' or ug.deleted
IS NULL)
and u.gallery_id = 1
and ug.gallery_id = 1 );
I had explain it and got the result :
Aggregate (cost=4836.61..4836.61 rows=1 width=24)
-> Nested Loop (cost=0.00..4836.61 rows=2
width=24)
-> Index Scan using
application_app_gallery_id on application app
(cost=0.00..3.05 rows=1 width=12)
-> Index Scan using acl_acl_gallery_id on acl
(cost=0.00..4830.80 rows=220 width=12)
SubPlan
-> Materialize (cost=6.10..6.10
rows=1 width=24)
-> Nested Loop
(cost=0.00..6.10 rows=1 width=24)
-> Index Scan using
user_group_ug_user_id on user_group u
(cost=0.00..2.02 rows=1 width=12)
-> Index Scan using
user_group_master_gallery_id on user_group_master ug
(cost=0.00..4.07 rows=1 width=12)
after rewrote it to :
select count(*)
from ACL a, APPLICATION app
where ACL_APP = app.app_id
and APP_INSTALLED = 'Y'
and ACL_LEVEL > 0
and ACL_GALLERY_ID = 1
and app.APP_GALLERY_ID = 1
and substr(app.app_order,1, 6 ) = '021101'
and app.app_order <> '021101'
and exists
(select u.ug_id
from user_group u, user_group_master
ug
where a.ACL_GRP = u.ug_id
and u.ug_user_id = 5170
and ug.ug_id = u.ug_id
and (ug.deleted = 'N' or ug.deleted
IS NULL)
and u.gallery_id = 1
and ug.gallery_id = 1 );
the explain was :
Aggregate (cost=4836.69..4836.69 rows=1 width=24)
-> Nested Loop (cost=0.00..4836.69 rows=2
width=24)
-> Index Scan using
application_app_gallery_id on application app
(cost=0.00..3.05 rows=1 width=12)
-> Index Scan using acl_acl_gallery_id on acl
a (cost=0.00..4830.89 rows=220 width=12)
SubPlan
-> Nested Loop (cost=0.00..6.10
rows=1 width=24)
-> Index Scan using
user_group_ug_id on user_group u (cost=0.00..2.02
rows=1 width=12)
-> Index Scan using
user_group_master_gallery_id on user_group_master ug
(cost=0.00..4.07 rows=1 width=12)
the performance seems no improvement.
the table ACL contains 106057 rows and index on
acl_gallery_id, acl_grp and acl_level .
the table APPLICATION contains 220 rows and index on
app_gallery_id and app_order .
-- Table: acl
CREATE TABLE "acl" (
"acl_id" numeric(10, 0) DEFAULT 0 NOT NULL,
"acl_app" numeric(10, 0),
"acl_grp" numeric(10, 0),
"acl_level" numeric(3, 0),
"acl_gallery_id" numeric(11, 0),
CONSTRAINT "acl_acl_app_key" UNIQUE ("acl_app",
"acl_grp"),
CONSTRAINT "acl_pkey" PRIMARY KEY ("acl_id"),
CONSTRAINT "acl_fk9992931283980" FOREIGN KEY
(acl_app) REFERENCES "application" (app_id) ON DELETE
NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY
IMMEDIATE,
CONSTRAINT "acl_ug" FOREIGN KEY (acl_grp) REFERENCES
"user_group_master" (ug_id) ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
-- Table: application
CREATE TABLE "application" (
"app_id" numeric(11, 0) NOT NULL,
"app_en_name" varchar(100) NOT NULL,
"app_tc_name" varchar(100),
"app_sc_name" varchar(100),
"app_menu" varchar(1),
"app_installed" varchar(1),
"app_order" varchar(50),
"app_alt" varchar(50),
"app_gif" varchar(100),
"app_link" varchar(100),
"app_initial" varchar(1),
"app_gallery_id" numeric(11, 0),
"app_terminator" varchar(1),
"app_en_length" numeric(4, 0),
"app_tc_length" numeric(4, 0),
"app_sc_length" numeric(4, 0),
"app_ext" varchar(1),
"app_type" varchar(30),
CONSTRAINT "application_pkey" PRIMARY KEY ("app_id")
);
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> > --- Stephan Szabo <sszabo@megazone23.bigpanda.com>
> > wrote:
> > >
> > > On Fri, 14 Dec 2001, MindTerm wrote:
> > >
> > > > Hi all,
> > > >
> > > > table :
> > > > application : 220 rows ;
> > > > usermenu : 6055 rows ;
> > >
> > > Well, I'd need the other tables involved in the
> > > function
> > > too, but to start with, try rewriting the
> queries in
> > > the
> > > function to use EXISTS rather than IN (see the
> faq),
> > > or
> > > bring them into a normal join with the other
> tables
> > > where
> > > possible.
>
> As a note, does rewriting the queries in the
> function
> to use exists rather than in help any?
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-18 07:39 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-18 07:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-18 09:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
0 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2001-12-18 07:50 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Mon, 17 Dec 2001, MindTerm wrote:
> select count(*)
> from ACL a, APPLICATION app
> where ACL_APP = app.app_id
> and APP_INSTALLED = 'Y'
> and ACL_LEVEL > 0
> and ACL_GALLERY_ID = 1
> and app.APP_GALLERY_ID = 1
> and substr(app.app_order,1, 6 ) = '021101'
> and app.app_order <> '021101'
> and exists
> (select u.ug_id
> from user_group u, user_group_master
> ug
> where a.ACL_GRP = u.ug_id
> and u.ug_user_id = 5170
> and ug.ug_id = u.ug_id
> and (ug.deleted = 'N' or ug.deleted
> IS NULL)
> and u.gallery_id = 1
> and ug.gallery_id = 1 );
I don't know if it'll help, but does:
select count(*)
from ACL a, APPLICATION app, user_group u,
user_group_master ug
where ACL_APP = app.app_id
and APP_INSTALLED = 'Y'
and ACL_LEVEL > 0
and ACL_GALLERY_ID = 1
and app.APP_GALLERY_ID = 1
and substr(app.app_order,1, 6 ) = '021101'
and app.app_order <> '021101'
and a.ACL_GRP = u.ug_id
and u.ug_user_id = 5170
and ug.ug_id = u.ug_id
and (ug.deleted = 'N' or ug.deleted
IS NULL)
and u.gallery_id = 1
and ug.gallery_id = 1;
give the same results as the original query. Maybe
that form will work faster. (I'm going to play with
it a little tomorrow, but since I don't have much data
in there, I'm not sure how well it'll translate)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-18 07:39 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-18 07:50 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-18 09:34 ` MindTerm <mindterm@yahoo.com>
0 siblings, 0 replies; 23+ messages in thread
From: MindTerm @ 2001-12-18 09:34 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Dear Stephan,
Yes, you are correct. After implementment of your
suggestion, the execution time was half of original
one ( 4 minutes -> 2 minutes ) . Then, I made some
other modifications, execution was about one minute.
The execution time of this type of operation was 3-4
times longer as compare to oracle. :(
M.T.
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Mon, 17 Dec 2001, MindTerm wrote:
>
> > select count(*)
> > from ACL a, APPLICATION app
> > where ACL_APP = app.app_id
> > and APP_INSTALLED = 'Y'
> > and ACL_LEVEL > 0
> > and ACL_GALLERY_ID = 1
> > and app.APP_GALLERY_ID = 1
> > and substr(app.app_order,1, 6 ) =
> '021101'
> > and app.app_order <> '021101'
> > and exists
> > (select u.ug_id
> > from user_group u,
> user_group_master
> > ug
> > where a.ACL_GRP = u.ug_id
> > and u.ug_user_id = 5170
> > and ug.ug_id = u.ug_id
> > and (ug.deleted = 'N' or
> ug.deleted
> > IS NULL)
> > and u.gallery_id = 1
> > and ug.gallery_id = 1 );
>
> I don't know if it'll help, but does:
>
> select count(*)
> from ACL a, APPLICATION app, user_group u,
> user_group_master ug
> where ACL_APP = app.app_id
> and APP_INSTALLED = 'Y'
> and ACL_LEVEL > 0
> and ACL_GALLERY_ID = 1
> and app.APP_GALLERY_ID = 1
> and substr(app.app_order,1, 6 ) =
> '021101'
> and app.app_order <> '021101'
> and a.ACL_GRP = u.ug_id
> and u.ug_user_id = 5170
> and ug.ug_id = u.ug_id
> and (ug.deleted = 'N' or
> ug.deleted
> IS NULL)
> and u.gallery_id = 1
> and ug.gallery_id = 1;
>
> give the same results as the original query. Maybe
> that form will work faster. (I'm going to play with
> it a little tomorrow, but since I don't have much
> data
> in there, I'm not sure how well it'll translate)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-14 06:47 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 23+ messages in thread
From: Stephan Szabo @ 2001-12-14 06:47 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Thu, 13 Dec 2001, MindTerm wrote:
> Hi all,
>
> I will try to assign more memory to shared memory,
> say 50M ~ 100M .
Forgot to mention. This depends on how much memory you've
actually got in the machine, but keep a bunch for backend
local memory stuff and system disk caching too. Going too
high on shared memory can also be bad.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-14 03:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-14 06:40 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 1 reply; 23+ messages in thread
From: Christopher Kings-Lynne @ 2001-12-14 03:43 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
Hmmm...
I have a database server for a website for which I am 'dedicating' at least
128MB of ram (I could say that it can have 256MB)
I have max_connections 64
and shared_buffers 256
and sort_mem 1024
Is that really small?
I have this SHM config:
options SYSVSHM
options SYSVMSG
options SYSVSEM
options SHMMAXPGS=16384 # 64MB shared mem?
#options SHMALL=1025 # max kb of shared mem
options SHMSEG=256 # 256 shared segs per proc
options SEMMNI=256 # 256 semaphore identifiers
options SEMMNS=512 # 512 semaphores in the system
options SEMMNU=256 # 256 undo structures in system
options SEMMAP=256 # 256 entries in semaphore map
How do you calculate the shared memory required by postgres given the
shared_buffers value???
Chris
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
> Sent: Friday, 14 December 2001 11:02 AM
> To: MindTerm
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] performance tuning in large function / transaction
>
>
> On Thu, 13 Dec 2001, MindTerm wrote:
>
> > I am writing a function (plpgsql) which equals to a
> > single transaction transaction in postgresql ( as I
> > known ). So I think that it is not a autocommmit mode.
> >
> > I have add following lines in postgresql.conf.
> >
> > postgresql.conf:
> > ====================
> > shared_buffers = 640
> > wal_buffers = 80
>
> It depends on how much memory you have, but even 640 is pretty
> low (I think that works out to 5M). Probably a few thousand
> is better if you've got the ram.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 19:17 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` Re: performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Re: performance tuning in large function / transaction Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:43 ` Re: performance tuning in large function / transaction Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2001-12-14 06:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 23+ messages in thread
From: Stephan Szabo @ 2001-12-14 06:40 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: MindTerm <mindterm@yahoo.com>; pgsql-sql
On Fri, 14 Dec 2001, Christopher Kings-Lynne wrote:
> Hmmm...
>
> I have a database server for a website for which I am 'dedicating' at least
> 128MB of ram (I could say that it can have 256MB)
>
> I have max_connections 64
> and shared_buffers 256
> and sort_mem 1024
>
> Is that really small?
>
> I have this SHM config:
>
> options SYSVSHM
> options SYSVMSG
> options SYSVSEM
>
> options SHMMAXPGS=16384 # 64MB shared mem?
> #options SHMALL=1025 # max kb of shared mem
> options SHMSEG=256 # 256 shared segs per proc
>
> options SEMMNI=256 # 256 semaphore identifiers
> options SEMMNS=512 # 512 semaphores in the system
> options SEMMNU=256 # 256 undo structures in system
> options SEMMAP=256 # 256 entries in semaphore map
>
> How do you calculate the shared memory required by postgres given the
> shared_buffers value???
IIRC each buffer is 8k, so 256 shared buffers is still pretty small.
sort_mem is wierd due to the way it's used (I think that's per sort, so
maybe you could get more than that per backend if a query had multiple
sort steps), but I think that's backend local memory not shared.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: performance tuning in large function / transaction
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
@ 2001-12-14 02:09 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 23+ messages in thread
From: Christopher Kings-Lynne @ 2001-12-14 02:09 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; pgsql-sql
Try running the entire set of statements within one transaction - that will
speed things up considerably.
You can also increase the number of buffers you are using, but that also
requires fiddling about with shared memory.
Chris
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of MindTerm
> Sent: Friday, 14 December 2001 12:27 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] performance tuning in large function / transaction
>
>
> Hi all,
>
> I am writing a large function which perform more
> than 2000 modify statements ( delete / insert ) . The
> execution time of this function is not acceptable. How
> can I config postgresql ( in postgresql.conf ?? ) so
> that the performance will be better ? The machine
> runing postgresql have spare memory and harddisk
> space.
>
>
> M.T.
>
> __________________________________________________
> Do You Yahoo!?
> Check out Yahoo! Shopping and Yahoo! Auctions for all of
> your unique holiday gifts! Buy at http://shopping.yahoo.com
> or bid at http://auctions.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2001-12-18 09:34 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-13 16:27 performance tuning in large function / transaction MindTerm <mindterm@yahoo.com>
2001-12-13 18:56 ` Jason Earl <jason.earl@simplot.com>
2001-12-13 19:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:46 ` MindTerm <mindterm@yahoo.com>
2001-12-14 03:02 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:34 ` MindTerm <mindterm@yahoo.com>
2001-12-14 06:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 08:05 ` MindTerm <mindterm@yahoo.com>
2001-12-14 08:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 09:06 ` MindTerm <mindterm@yahoo.com>
2001-12-14 14:44 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 02:58 ` MindTerm <mindterm@yahoo.com>
2001-12-17 03:41 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 04:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-17 05:39 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-12-17 06:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-18 07:39 ` MindTerm <mindterm@yahoo.com>
2001-12-18 07:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-18 09:34 ` MindTerm <mindterm@yahoo.com>
2001-12-14 06:47 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 03:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-14 06:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-14 02:09 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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