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

Re: optimizing selects on time-series data in Pg

From: Josh Berkus <josh(at)agliodbs(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 17:33:42
Message-ID: 200308011033.42917.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
Aidtya,

> Here's what I did:
>
> - I did a vacuum
>
> - did a select count(*) for a few different virtualhosts to see how many
> rows were being dealt with
>
> - run explain analyze on each of those select count(*) and noted the
> difference, ie. the planner decided that a select on a virtual host with
> many more rows was better served by sequential scan than index scan

From the look of it, the database is doing the best it can on your hardware 
and current tuning setup.  I don't think you can speed this up on the SQL 
side, unless you implement an aggregate caching scheme.

It's possible that you could move the break point btw. seq scan and index scan 
slightly by increasing shared_buffers and effective_cache_size.  What are 
those variables now?  And what's your hardware?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

sfpug by date

Next:From: AdityaDate: 2003-08-01 17:51:48
Subject: Re: optimizing selects on time-series data in Pg
Previous:From: AdityaDate: 2003-08-01 17:13:39
Subject: Re: optimizing selects on time-series data in Pg

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