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

Re: [PERFORM] Query tuning

From: "Subbiah, Stalin" <SSubbiah(at)netopia(dot)com>
To: "Dave Dutcher" <dave(at)tridecap(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: [PERFORM] Query tuning
Date: 2006-08-24 04:44:20
Message-ID: B949C470120CA7499A211214D76FBA5801544869@mxca2.corp.netopia.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
Changing limit or offset to a small number doesn't have any change in
plans. Likewise enable_seqscan to false. They still take 8-10 mins to
runs. 

-----Original Message-----
From: Dave Dutcher [mailto:dave(at)tridecap(dot)com] 
Sent: Wednesday, August 23, 2006 4:20 PM
To: Subbiah, Stalin
Cc: pgsql-performance(at)postgresql(dot)org
Subject: RE: [PERFORM] Query tuning

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(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Subbiah, 
> Stalin
> Sent: Wednesday, August 23, 2006 1:03 PM
> To: Chris
> Cc: pgsql-performance(at)postgresql(dot)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(at)gmail(dot)com]
> Sent: Tuesday, August 22, 2006 8:06 PM
> To: Subbiah, Stalin
> Cc: pgsql-performance(at)postgresql(dot)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
> 


Responses

pgsql-performance by date

Next:From: Jason MinionDate: 2006-08-24 05:30:56
Subject: Re: [PERFORM] Query tuning
Previous:From: Dan LangilleDate: 2006-08-24 02:42:49
Subject: Re: Forcing index usage without 'enable_hashjoin = FALSE'

pgsql-admin by date

Next:From: Jason MinionDate: 2006-08-24 05:30:56
Subject: Re: [PERFORM] Query tuning
Previous:From: Tom LaneDate: 2006-08-23 23:39:02
Subject: Re: pg_hba changes not honored

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