Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3D3D99FBAED for ; Wed, 18 Jul 2007 07:51:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 91646-07 for ; Wed, 18 Jul 2007 07:51:10 -0300 (ADT) X-Greylist: delayed 00:06:39.443545 by SQLgrey-1.7.5 Received: from mail1.slb.deg.dub.stisp.net (mail1.slb.deg.dub.stisp.net [84.203.253.98]) by postgresql.org (Postfix) with SMTP id 8FBF39FBA96 for ; Wed, 18 Jul 2007 07:51:10 -0300 (ADT) Received: (qmail 46138 invoked from network); 18 Jul 2007 10:44:27 -0000 Received: from unknown (HELO holyman.cobbled.net) (84.203.180.117) by mail1.slb.deg.dub.stisp.net with SMTP; 18 Jul 2007 10:44:27 -0000 Received: by holyman.cobbled.net (Postfix, from userid 16385) id 7471617081; Wed, 18 Jul 2007 10:44:18 +0000 (UTC) Date: Wed, 18 Jul 2007 10:44:18 +0000 From: n0g0013 To: Mary Anderson Cc: pgsql-admin@postgresql.org Subject: Re: am i creating a performance bottleneck? Message-ID: <20070718104418.GD16194@holyman.cobbled.net> References: <469D5F4F.10307@demog.berkeley.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <469D5F4F.10307@demog.berkeley.edu> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200707/203 X-Sequence-Number: 26184 On 17.07-17:31, Mary Anderson wrote: [ ... ] > I am most worried about the second select, with its 'NOT EXISTS' > statement slowing everything down. I would put appropriate indexes on > this -- namely an index on series for data and an index on dhd for > da_id. My user community is a bunch of academics, so I am not under > the performance constraints I would have for a business application. > > Would it help performance if I denormalized the database by attaching > series to the data_has_dimensions table? to be honest i couldn't really understand your SQL but that probably says more about me than you. either way i don't think you need to worry about the NOT EXISTS statement. generally, serialising select statements is better than joining and would suggest that if your series data has 10,000 rows (assuming 10,00 was a typo) then i wouldn't merge that data with another table (it also will simply mean a larger, disjoint table, essentially equivelant to the join table required your queries and thus may well cost you in overall performance). i would suggest you try to restructure you queries to serialise the 'series' select joins (and preferably only doing it once), thereby reducing the final join table to a minimum. -- t t w