From: | Bryce Nesbitt <bryce2(at)obviously(dot)com> |
---|---|
To: | sql pgsql <pgsql-sql(at)postgresql(dot)org> |
Subject: | index compatible date_trunc in postgres? |
Date: | 2008-12-19 05:46:45 |
Message-ID: | 494B3545.8030701@obviously.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2008-12-19 05:52:24 | Re: index compatible date_trunc in postgres? |
Previous Message | Steve Crawford | 2008-12-18 16:15:18 | Re: Re: How to insert Images(bnp,png,etc) into Postgresql and how to retrive the inserted Imaged using C#.net |