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'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql