Re: index compatible date_trunc in postgres?

Tom Lane wrote:
> Why are you worrying?  The old method surely didn't get indexed either.

Continuous improvement?

Since there already IS an index available, I figure I might as well use 
it, especially since this DB had real performance issues.  The table 
itself is medium sized in our world, at 647596 rows.


Scott Marlowe wrote:
> You can create an index on date_trunc (on timestamp without timezone,
> but not on timestamp with timezone since it's not immutable)
That could work...
create index brycetest5 on contexts (date_trunc('month',publication_date));
(13 MB index, same size as the index on the non-truncated date).

Unfortunately complains about immutability:
create index brycetest5 on contexts (to_char(publication_date,'YYYY-MM'));

But in this case I think I'll rewrite the query into a range, and stick 
with the present index. Thanks for your help.