Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBI7oFm28389 for ; Tue, 18 Dec 2001 02:50:15 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id BA791D5E8; Mon, 17 Dec 2001 23:50:15 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id AE25B5BF1; Mon, 17 Dec 2001 23:50:15 -0800 (PST) Date: Mon, 17 Dec 2001 23:50:15 -0800 (PST) From: Stephan Szabo To: MindTerm Cc: Subject: Re: performance tuning in large function / transaction In-Reply-To: <20011218073907.397.qmail@web20209.mail.yahoo.com> Message-ID: <20011217234256.L57934-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200112/193 X-Sequence-Number: 6016 On Mon, 17 Dec 2001, MindTerm wrote: > 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 ); I don't know if it'll help, but does: select count(*) from ACL a, APPLICATION app, user_group u, user_group_master ug 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 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; give the same results as the original query. Maybe that form will work faster. (I'm going to play with it a little tomorrow, but since I don't have much data in there, I'm not sure how well it'll translate)