From: Chris <dmagick@gmail.com>
To: Subbiah, Stalin <SSubbiah@netopia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning
Date: Wed, 23 Aug 2006 11:37:13 +1000
Message-ID: <44EBB149.5090205@gmail.com> (raw)
In-Reply-To: <B949C470120CA7499A211214D76FBA5801544418@mxca2.corp.netopia.com>
References: <B949C470120CA7499A211214D76FBA5801544418@mxca2.corp.netopia.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmagick@gmail.com, SSubbiah@netopia.com
Subject: Re: Query tuning
In-Reply-To: <44EBB149.5090205@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox