Re: performance tuning in large function / transaction

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(at)megazone23(dot)bigpanda(dot)com>
wrote:
> > --- Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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(at)postgresql(dot)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