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@xxxxxxxxxxxx] Sent: Wednesday, August 23, 2006 4:20 PM To: Subbiah, Stalin Cc: pgsql-performance@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Subbiah, > Stalin > Sent: Wednesday, August 23, 2006 1:03 PM > To: Chris > Cc: pgsql-performance@xxxxxxxxxxxxxx > 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@xxxxxxxxx] > Sent: Tuesday, August 22, 2006 8:06 PM > To: Subbiah, Stalin > Cc: pgsql-performance@xxxxxxxxxxxxxx > 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 >
