Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 871F59FB63D for ; Tue, 17 Jul 2007 21:31:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 75526-05 for ; Tue, 17 Jul 2007 21:30:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from transition.DEMOG.Berkeley.EDU (DEMOG.Berkeley.EDU [128.32.93.50]) by postgresql.org (Postfix) with ESMTP id 936539FB564 for ; Tue, 17 Jul 2007 21:31:08 -0300 (ADT) Received: from transition.DEMOG.Berkeley.EDU (localhost.localdomain [127.0.0.1]) by transition.DEMOG.Berkeley.EDU (Postfix) with ESMTP id DF6DB1394C for ; Tue, 17 Jul 2007 17:31:06 -0700 (PDT) Received: from [192.168.1.26] (generation.DEMOG.Berkeley.EDU [192.168.1.26]) (using SSLv3 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by transition.DEMOG.Berkeley.EDU (Postfix) with ESMTP id AA1C1138E2 for ; Tue, 17 Jul 2007 17:31:06 -0700 (PDT) Message-ID: <469D5F4F.10307@demog.berkeley.edu> Date: Tue, 17 Jul 2007 17:31:11 -0700 From: Mary Anderson User-Agent: Thunderbird 1.5 (X11/20051201) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: am i creating a performance bottleneck? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.112 tagged_above=0 required=5 tests=AWL=0.111, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200707/194 X-Sequence-Number: 26175 Hi all, In order to save a fair amount of space I am creating a schema against which I will want to run the following simplified query. Think of the size of the tables as data ~ 30 M rows, series has 10,00 entries, data_has_dimensions has 300 K rows, dimensions is tiny select population = da.value, residence = di.value from data da, data_has_dimension dhd, dimensions di, series si where da.series_id = si.series_id and si.series = 'my-series' and dhd.data_id = da.data_id and dhd.dimension_id = di.dimension_id and di.dimension = 'residence' UNION select population = da.value, residence = 'total' where da.series_id = si.series_id and si.series = 'my_series' and NOT EXISTS(select dhd.da_id from data_has_dimensions dhd, dimensions di where di.dimension = 'residence' and dhd.dimension_id = di.dimension_id and dhd.da_id = da.da_id) 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? Thanks, Mary Anderson