Re: [PERFORM] Query tuning

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(dot)minion(at)sigler(dot)com

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Subbiah, Stalin
Sent: Wednesday, August 23, 2006 11:44 PM
To: Dave Dutcher
Cc: pgsql-performance(at)postgresql(dot)org; pgsql-admin(at)postgresql(dot)org
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(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
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings