Re: *very* slow query to summarize data for a month ...

On 11 Nov 2003, Greg Stark wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
>
> > On Tue, 11 Nov 2003, Greg Stark wrote:
> >
> > > Actually you might be able to get the same effect using function indexes
> > > like:
> > >
> > > create index i on traffic_log (month_trunc(runtime), company_id)
> >
> > had actually thought of that one ... is it something that is only
> > available in v7.4?
>
> Hum, I thought you could do simple functional indexes like that in 7.3, but
> perhaps only single-column indexes.
>
> In any case, given your situation I would seriously consider putting a
> "month" integer column on your table anyways. Then your index would be a
> simple (month, company_id) index.

In 7.3 and before, you had to use only column names as inputs, so you
could cheat:

alter table test add alp int;
alter table test add omg int;
update test set alp=0;
update test set omg=13;

and then create a functional index:

create index test_xy on test (substr(info,alp,omg));

select * from test where substr(info,alp,omg)=='abcd';