From: Dave Dutcher <dave@tridecap.com>
To: 'Subbiah, Stalin' <SSubbiah@netopia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning
Date: Wed, 23 Aug 2006 18:19:59 -0500
Message-ID: <02a501c6c70a$a7ff61d0$8300a8c0@tridecap.com> (raw)
In-Reply-To: <B949C470120CA7499A211214D76FBA58015445AD@mxca2.corp.netopia.com>
It seems to me that what would work best is an index scan backward on the
eventtime index. I don't see why that wouldn't work for you, maybe the
planner is just esitmating the seq scan and sort is faster for some reason.
What does EXPLAIN say if you use a small limit and offset like 10? Or what
does EXPLAIN say if you first run "set enable_seqscan=false;" (If you get
the same plan, then I wouldn't bother running EXPLAIN ANALYZE, but if you
get a different plan I would run EXPLAIN ANALYZE to see if the new plan is
any faster.)
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Subbiah, Stalin
> Sent: Wednesday, August 23, 2006 1:03 PM
> To: Chris
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
>
> I get the same plan after running vacuum analyze. Nope, I don't have
> index on objdomainid, objid and userdomainid. Only eventime has it.
>
> -----Original Message-----
> From: Chris [mailto:dmagick@gmail.com]
> Sent: Tuesday, August 22, 2006 8:06 PM
> To: Subbiah, Stalin
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
> 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/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
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: dave@tridecap.com, SSubbiah@netopia.com
Subject: Re: Query tuning
In-Reply-To: <02a501c6c70a$a7ff61d0$8300a8c0@tridecap.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