Re: Query tuning

On Wednesday 19 August 2009 11:31:30 Nikolas Everett wrote:
> 2009/8/19 Grzegorz Jaśkiewicz <gryzman@gmail.com>
>
> > that seems to be the killer:
> >
> > and time >= extract ('epoch' from timestamp '2009-08-12')
> > and time < extract ('epoch' from timestamp '2009-08-13' )
> >
> > You probably need an index on time/epoch:
> >
> > CREATE INDEX foo ON table(extract ('epoch' from timestamp time );
>
> It looks like those extracts just make constant integer times. You probably
> just create an index on the time column.
>
> Also, why not store times as timestamps?
>
> > or something like that, vacuum analyze and retry.
> >
> > --
> > Sent via pgsql-performance mailing list
> > (pgsql-performance@postgresql.org) To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance


We do have an index on url_hits.time

not sure why timestamps were not used, I was not here for the design phase.


Thx