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

Re: optimizing selects on time-series data in Pg

From: Aditya <aditya(at)grot(dot)org>
To: sfpug(at)postgresql(dot)org
Subject: Re: optimizing selects on time-series data in Pg
Date: 2003-08-01 19:29:24
Message-ID: 20030801192924.GA29635@mighty.grot.org (view raw or flat)
Thread:
Lists: sfpug
On Fri, Aug 01, 2003 at 11:51:17AM -0700, Stephan Szabo wrote:
> On Fri, 1 Aug 2003, Aditya wrote:
> 
> > On Fri, Aug 01, 2003 at 11:28:05AM -0700, Stephan Szabo wrote:
> > > Does it lower the realtime after a set enable_seqscan=off; (also an
> > > explain analyze would let us see if there's a type difference that causes
> > > it to not want to use the index for the date comparison).
> >
> > hm, that's interesting, it doesn't want to use the combined index if I turn
> > off seqscan and in any case it isn't any better doing an index scan with just
> > the virtualhost:
> >
> > zp1139=> set enable_seqscan=off; SET VARIABLE
> > zp1139=> explain analyze select count(*) from zp_log where virtualhost =
> > 'www.bloki.com' and timestamp > (now() - interval '2 days');
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate (cost=902991.72..902991.72 rows=1 width=0) (actual
> > time=18876.95..18876.95 rows=1 loops=1)
> >   -> Index Scan using zp_log_vhost on zp_log (cost=0.00..902794.58 rows=78856
> > width=0) (actual time=45.12..18783.10 rows=23241 loops=1)
> > Total runtime: 18878.03 msec
> 
> Errm, what version are you running?  A 7.2 variety? I don't think now() is
> marked as cacheable in 7.2 which means it won't be considered an indexable
> condition which shoots any such plan out of the water without using a
> separate function that is marked cacheable that just returns the value of
> now().

yup, using 7.2.2 -- okay, that makes sense then. I'll try using the separate
function.

Thanks,
Adi

In response to

sfpug by date

Next:From: AdityaDate: 2003-08-01 19:31:13
Subject: Re: optimizing selects on time-series data in Pg
Previous:From: Stephan SzaboDate: 2003-08-01 18:51:17
Subject: Re: optimizing selects on time-series data in Pg

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