From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>, Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: *very* slow query to summarize data for a month ... |
Date: | 2003-11-11 21:25:06 |
Message-ID: | Pine.LNX.4.33.0311111353400.29410-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Fankhauser - Doxpop | 2003-11-11 22:26:48 | Seeking help with a query that take too long |
Previous Message | Greg Stark | 2003-11-11 19:51:22 | Re: *very* slow query to summarize data for a month ... |