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.