On Thu, Dec 18, 2008 at 10:46 PM, Bryce Nesbitt <bry...@obviously.com> wrote: > I've got a legacy app that does 8.3 incompatible date searches like so: > explain select count(*) from contexts where publication_date like '2006%'; > explain select count(*) from contexts where publication_date like > '2006-09%'; > > I've got my choice of refactoring, but all these share the same > sequential scan limitation: > explain select count(*) from contexts where publication_date::text LIKE > '2006%'; > explain select count(*) from contexts where > date_trunc('year',publication_date) = '2006-01-01'; > explain select count(*) from contexts where extract('year' from > publication_date) = '2006'; > > Are there any other index compatible methods, other than turning it into > a range search? > explain select count(*) from contexts where publication_date >= > '2006-01-01' and publication_date < '2007-01-01'; > explain select count(*) from contexts where publication_date >= > '2006-09-01' and publication_date < '2006-09-31 24:00:00';
You can create an index on date_trunc (on timestamp without timezone, but not on timestamp with timezone since it's not immutable) create index mytable_datetrunc_month on mytable (date_trunc('month', timestampfield)); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql