From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Aditya <aditya(at)grot(dot)org> |
Cc: | <sfpug(at)postgresql(dot)org> |
Subject: | Re: optimizing selects on time-series data in Pg |
Date: | 2003-08-01 18:51:17 |
Message-ID: | 20030801114229.B56675-100000@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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().
From | Date | Subject | |
---|---|---|---|
Next Message | Aditya | 2003-08-01 19:29:24 | Re: optimizing selects on time-series data in Pg |
Previous Message | Josh Berkus | 2003-08-01 18:42:22 | Re: optimizing selects on time-series data in Pg |