Re: Optimize date query for large child tables: GiST or GIN?

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: David Jarvis <thangalin(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-20 13:03:11
Message-ID: alpine.DEB.2.00.1005201355020.23334@aragorn.flymine.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)).

Matthew

--
Here we go - the Fairy Godmother redundancy proof.
-- Computer Science Lecturer

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-05-20 13:56:50 Re: Optimize date query for large child tables: GiST or GIN?
Previous Message Thom Brown 2010-05-20 08:33:15 Re: Optimize date query for large child tables: GiST or GIN?