Re: Query help

Sure I can provide those details. I have seen this query running 5+
minutes for different values for doaminID too. Its just that it happens
at random and gets fixed within few mins.

Shared buffer=8G, effective cache size=4G. Optimizer/autovaccum settings
are defaults

relname | relpages | reltuples
------------------------------+----------+-----------
ct_objects_id_u1 | 11906 | 671919
ix_objects_altname | 13327 | 671919
ix_objects_domainid_name | 24714 | 671919
ix_objects_key3 | 9891 | 671919
ix_objects_name | 11807 | 671919
ix_objects_type_lastmodified | 38640 | 671919
ix_objects_user1 | 20796 | 671919
ix_objects_user2 | 20842 | 671919
objects | 111873 | 671919

This database resides on a RAID 1+0 storage with 10 disks (5+5).

Let me know if you need any other information.

Thanks Kevin.

Stalin

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Monday, August 03, 2009 12:48 PM
To: Subbiah Stalin-XCGF84; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Query help

"Subbiah Stalin-XCGF84" <SSubbiah(at)Motorola(dot)com> wrote:

> Not sure what's wrong in below execution plan but at times the query
> runs for 5 minutes to complete and after a while it runs within a
> second or two.

The plan doesn't look entirely unreasonable for the given query,
although it's hard to be sure of that without seeing the table
definitions. Given the plan, the times look to be about what I'd expect
for uncached and cached timings. (That is, on subsequent runs, the data
is sitting in RAM, so you don't need to access the hard
drives.)

If the initial run time is unacceptable for your environment, and
there's no way to have the cached "primed" when it matters, please give
more details on your table layouts, and perhaps someone can make a
useful suggestion.

> Pg 8.2.7, Sol10.

One quick suggestion -- upgrade your PostgreSQL version if at all
possible. The latest bug-fix version of 8.2 is currently 8.2.13, and
there are significant performance improvements in 8.3 and the newly
released 8.4.

-Kevin