DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=r8Y+ADdGjvIxwOtTFx9pNale0sVY7iB30m+4JOwrq7cq0XHWUipxq6DY825L09eQ8hHGOTRNxtsOG9zWapBzsAKSGvat7s7R2VePc+lUK7Ht3RebmXNn+tVQpXSjElNIb2dsndzZNslWRoflv6S+SnsAg/TpM0h3sB/2VUszORs=
Message-ID: <44EBB149.5090205@gmail.com>
Date: Wed, 23 Aug 2006 11:37:13 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: "Subbiah, Stalin" <SSubbiah@netopia.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Query tuning
References: <B949C470120CA7499A211214D76FBA5801544418@mxca2.corp.netopia.com>
In-Reply-To: <B949C470120CA7499A211214D76FBA5801544418@mxca2.corp.netopia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Subbiah, Stalin wrote:
> Hello All,
> 
> This query runs forever and ever. Nature of this table being lots of
> inserts/deletes/query, I vacuum it every half hour to keep the holes
> reusable and nightly once vacuum analyze to update the optimizer. We've
> got index on eventtime only. Running it for current day uses index range
> scan and it runs within acceptable time. Below is the explain of the
> query. Is the order by sequencenum desc prevents from applying limit
> optimization?
> 
> explain SELECT *
> FROM EVENTLOG 
> WHERE EVENTTIME>'07/23/06 16:00:00' 
> AND  EVENTTIME<'08/22/06 16:00:00' 
> AND  (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA' 
>         OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA' 
>         OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA') 
> ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
>  
> QUERY PLAN
> 
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> -------------------------------------------------------------
>  Limit  (cost=15546930.29..15546931.54 rows=500 width=327)
>    ->  Sort  (cost=15546930.29..15581924.84 rows=13997819 width=327)
>          Sort Key: eventtime, sequencenum
>          ->  Seq Scan on eventlog  (cost=0.00..2332700.25 rows=13997819
> width=327)
>                Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
> without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
> without time zone) AND (((objdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> (5 rows)
> 
> Thanks,
> Stalin
> Pg version 8.0.1, suse 64bit.

Firstly you should update to 8.0.8 - because it's in the same stream you 
won't need to do a dump/initdb/reload like a major version change, it 
should be a simple upgrade.

Can you send explain analyze instead of just explain?

It sounds like you're not analyz'ing enough - if you're doing lots of 
updates/deletes/inserts then the statistics postgresql uses to choose 
whether to do an index scan or something else will quickly be outdated 
and so it'll have to go back to a full table scan every time..

Can you set up autovacuum to handle that for you more regularly?

-- 
Postgresql & php tutorials
http://www.designmagick.com/