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