Re: performance tuning in large function / transaction - Mailing list 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