Re: performance tuning in large function / transaction

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