X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 40B5130FF15 for ; Sat, 28 Jun 2003 09:36:03 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 65253-07 for ; Sat, 28 Jun 2003 06:35:51 -0300 (ADT) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 5298430F472 for ; Sat, 28 Jun 2003 06:35:49 -0300 (ADT) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h5S9ZnQ10215 for ; Sat, 28 Jun 2003 15:05:49 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h5S9ZmG10210 for ; Sat, 28 Jun 2003 15:05:49 +0530 From: Shridhar Daithankar To: Subject: Re: Large querie with several EXISTS which will be often runned Date: Sat, 28 Jun 2003 15:05:00 +0530 User-Agent: KMail/1.5.2 References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200306281505.00407.shridhar_daithankar@nospam.persistent.co.in> X-Archive-Number: 200306/201 X-Sequence-Number: 2285 On Saturday 28 June 2003 14:47, Bruno BAGUETTE wrote: > Do you mean this query ? > > SELECT > products_options_groups.pk_prdoptgrp_id,products_options_groups.prdoptgr > p_name > FROM products_options_groups > WHERE EXISTS > ( > SELECT * > FROM products_options_classification > INNER JOIN products_options ON products_options.pk_prdopt_id = > products_options_classification.fk_prdopt_id > WHERE products_options_classification = > products_options_groups.pk_prdoptgrp_id > AND products_options.fk_prd_id = [A PRODUCT ID WRITTEN HERE BY > MY APP] > ) > ORDER BY products_options_groups.prdoptgrp_name; You can try SELECT products_options_groups.pk_prdoptgrp_id,products_options_groups.prdoptgr p_name FROM products_options_groups WHERE ( SELECT count(*) FROM products_options_classification INNER JOIN products_options ON products_options.pk_prdopt_id = products_options_classification.fk_prdopt_id WHERE products_options_classification = products_options_groups.pk_prdoptgrp_id AND products_options.fk_prd_id = [A PRODUCT ID WRITTEN HERE BY MY APP] )>0 ORDER BY products_options_groups.prdoptgrp_name; The count(*) trick will make it just another subquery and hopefully any performance issues with exists/in does not figure. Some of those issues are fixed in 7.4/CVS head though. HTH Shridhar