Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E3B266334D9 for ; Thu, 20 May 2010 10:57:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32968-04 for ; Thu, 20 May 2010 13:56:54 +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 3153C632C1D for ; Thu, 20 May 2010 10:56:55 -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 o4KDuodw003388; Thu, 20 May 2010 09:56:50 -0400 (EDT) To: Matthew Wakeling cc: David Jarvis , pgsql-performance@postgresql.org Subject: Re: Optimize date query for large child tables: GiST or GIN? In-reply-to: References: Comments: In-reply-to Matthew Wakeling message dated "Thu, 20 May 2010 14:03:11 +0100" Date: Thu, 20 May 2010 09:56:50 -0400 Message-ID: <3387.1274363810@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/111 X-Sequence-Number: 38695 Matthew Wakeling writes: > On Wed, 19 May 2010, David Jarvis wrote: >> extract( YEAR FROM m.taken ) BETWEEN 1900 AND 2009 AND > That portion of the WHERE clause cannot use an index on m.taken. Postgres > does not look inside functions (like extract) to see if something > indexable is present. To get an index to work, you could create an index > on (extract(YEAR FROM m.taken)). What you really need to do is not do date arithmetic using text-string operations. The planner has no intelligence about that whatsoever. Convert the operations to something natural using real date or timestamp types, and then look at what indexes you need. regards, tom lane