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: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: optimizing selects on time-series data in Pg
Date: 2003-08-01 18:38:57
Message-ID: 20030801183857.GB29233@mighty.grot.org (view raw or flat)
Thread:
Lists: sfpug
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

EXPLAIN
zp1139=> set enable_seqscan=on; 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=59157.66..59157.66 rows=1 width=0) (actual
time=38736.04..38736.04 rows=1 loops=1)
  -> Seq Scan on zp_log (cost=0.00..58960.52 rows=78856 width=0) (actual
time=0.19..38611.08 rows=23203 loops=1)
Total runtime: 38737.25 msec

EXPLAIN
zp1139=> \d zp_log_vhost_timestamp
     Index "zp_log_vhost_timestamp"
   Column    |           Type
-------------+--------------------------
 virtualhost | text
 timestamp   | timestamp with time zone
btree


In response to

Responses

sfpug by date

Next:From: Josh BerkusDate: 2003-08-01 18:42:22
Subject: Re: optimizing selects on time-series data in Pg
Previous:From: AdityaDate: 2003-08-01 18:31:10
Subject: Re: optimizing selects on time-series data in Pg

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