Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group