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 13:05:34 +1000
Message-ID: <44EBC5FE.9020404@gmail.com> (raw)
In-Reply-To: <B949C470120CA7499A211214D76FBA580154443E@mxca2.corp.netopia.com>
References: <B949C470120CA7499A211214D76FBA580154443E@mxca2.corp.netopia.com>
Subbiah, Stalin wrote:
> Actually these servers will be upgraded to 8.1.4 in couple of months.
even so, you could get some bad data in there.
http://www.postgresql.org/docs/8.0/static/release.html . Go through the
old release notes and you'll find various race conditions, crashes etc.
> Here you go with explain analyze.
>
> # explain analyze 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 500;
>
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> -------------------------------------------------------------
> Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
> time=427771.568..427772.904 rows=500 loops=1)
> -> Sort (cost=15583108.89..15618188.88 rows=14031998 width=327)
> (actual time=427770.504..427771.894 rows=1000 loops=1)
> Sort Key: eventtime, sequencenum
> -> Seq Scan on eventlog (cost=0.00..2334535.17 rows=14031998
> width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
> 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)))
> Total runtime: 437884.134 ms
> (6 rows)
If you analyze the table then run this again what plan does it come back
with?
I can't read explain output properly but I suspect (and I'm sure I'll be
corrected if need be) that the sort step is way out of whack and so is
the seq scan because the stats aren't up to date enough.
Do you have an index on objdomainid, objid and userdomainid (one index
per field) ? I wonder if that will help much.
--
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: <44EBC5FE.9020404@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