Received: from web20204.mail.yahoo.com (web20204.mail.yahoo.com [216.136.226.59]) by postgresql.org (8.11.3/8.11.4) with SMTP id fBH2wqm54848 for ; Sun, 16 Dec 2001 21:58:53 -0500 (EST) (envelope-from mindterm@yahoo.com) Message-ID: <20011217025856.23363.qmail@web20204.mail.yahoo.com> Received: from [203.85.100.3] by web20204.mail.yahoo.com via HTTP; Sun, 16 Dec 2001 18:58:56 PST Date: Sun, 16 Dec 2001 18:58:56 -0800 (PST) From: MindTerm Subject: Re: performance tuning in large function / transaction To: Stephan Szabo Cc: pgsql-sql@postgresql.org In-Reply-To: <20011214064148.J11758-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200112/170 X-Sequence-Number: 5993 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 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