You really need to index the three fields you are restricting with your select query (OBJDOMAINID, OBJID and USERDOMAINID). Depending on whether or not you have other queries that filter for one of the three fields but not the others, you might want to have separate indexes across each of the fields. Also, if those types are not CHAR/VARCHAR/TEXT/similar, try casting your values to the types for those fields (ie, OBJDOMAINID='somethinghere'::WVARCHAR, and should be much less necessary with pgsql v8+). Since you don't have indexes on those fields, the only thing the query planner can do is a full table scan and for each record check the field values. With indexes it will be able to filter by those values first and then sort the remaining values. On the other hand, if most of your records have the same values for your filter fields (ie only a handful of values dispersed amongst millions of rows) then you may be back to the seq scan. Jason Minion jason.minion@xxxxxxxxxx -----Original Message----- From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Subbiah, Stalin Sent: Wednesday, August 23, 2006 11:44 PM To: Dave Dutcher Cc: pgsql-performance@xxxxxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: [ADMIN] [PERFORM] Query tuning 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 > ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
