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(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.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go 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