Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 0D2F563633F for ; Thu, 20 May 2010 16:53:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 09304-05 for ; Thu, 20 May 2010 19:52:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id CA076635BB3 for ; Thu, 20 May 2010 16:52:52 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o4KJqo19007768; Thu, 20 May 2010 15:52:50 -0400 (EDT) To: David Jarvis cc: Thom Brown , pgsql-performance@postgresql.org Subject: Re: Optimize date query for large child tables: GiST or GIN? In-reply-to: References: <7143.1274382139@sss.pgh.pa.us> Comments: In-reply-to David Jarvis message dated "Thu, 20 May 2010 12:45:53 -0700" Date: Thu, 20 May 2010 15:52:50 -0400 Message-ID: <7767.1274385170@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/121 X-Sequence-Number: 38705 David Jarvis writes: > I was thinking that I could add three more columns to the measurement table: > year_taken, month_taken, day_taken > Then index those. That should allow me to avoid extracting years, months, > and days from the *m.taken* date column. You could, but I don't think there's any advantage to that versus putting indexes on extract(day from taken) etc. The extra fields eat more space in the table proper, and the functional index isn't really any more expensive than a plain index. Not to mention that you can have bugs with changing the date and forgetting to update the derived columns, etc etc. regards, tom lane