Skip site navigation (1) Skip section navigation (2)

Re: Query tuning

From: Kevin Kempter <kevink(at)consistentstate(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query tuning
Date: 2009-08-19 17:37:58
Message-ID: 200908191137.58565.kevink@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wednesday 19 August 2009 11:31:30 Nikolas Everett wrote:
> 2009/8/19 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)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(at)postgresql(dot)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




In response to

Responses

pgsql-performance by date

Next:From: Jaime CasanovaDate: 2009-08-20 00:25:11
Subject: [PERFORMANCE] how to set wal_buffers
Previous:From: Kevin KempterDate: 2009-08-19 17:36:55
Subject: Re: Query tuning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group