Re: Query help

Server has 32G memory and it's a dedicated to run PG and no other
application is sharing this database. I have checked checkpoints and
they don't occur during those slow query runtimes. Checkpoint_segments
is set 128. here is quick snap from vmstat.

 # vmstat 5 5
 kthr      memory            page            disk          faults
cpu
 r b w   swap  free  re  mf pi po fr de sr 1m 1m 1m m1   in   sy   cs us
sy id
 0 0 0 56466032 25908072 59 94 516 13 13 0 0 10 3 59 1  480  443  500  1
1 98
 0 0 0 51377520 20294328 6 8 0 32 32  0  0  0  4  1  0  368  185  361  0
1 99
 0 0 0 56466032 25908064 59 94 516 13 13 0 0 1 10 3 59  480  443  500  1
1 98
 0 0 0 51376984 20294168 57 427 0 16 16 0 0 0  0  1  0  380  781  396  1
1 98
 0 0 0 51376792 20294208 112 1131 2 50 50 0 0 0 0 5  2  398 2210  541  4
3 92

\d output --

                 Table "public.objects"
    Column    |            Type             | Modifiers
--------------+-----------------------------+-----------
 id           | character varying(28)       | not null
 name         | character varying(50)       | not null
 altname      | character varying(50)       |
 type         | character varying(3)        |
 domainid     | character varying(28)       | not null
 status       | smallint                    |
 dbver        | integer                     |
 created      | timestamp without time zone |
 lastmodified | timestamp without time zone |
 assignedto   | character varying(28)       |
 status2      | smallint                    |
 key1         | character varying(25)       |
 key2         | character varying(25)       |
 key3         | character varying(64)       |
 oui          | character varying(6)        |
 prodclass    | character varying(64)       |
 user1        | character varying(50)       |
 user2        | character varying(50)       |
 data0        | character varying(2000)     |
 data1        | character varying(2000)     |
 longdata     | character varying(1)        |
Indexes:
    "ct_objects_id_u1" PRIMARY KEY, btree (id), tablespace
"nbbs_index_data"
    "ix_objects_altname" btree (altname), tablespace "nbbs_index_data"
    "ix_objects_domainid_name" btree (domainid, upper(name::text)),
tablespace "nbbs_index_data"
    "ix_objects_key3" btree (upper(key3::text)), tablespace
"nbbs_index_data"
    "ix_objects_name" btree (upper(name::text) varchar_pattern_ops),
tablespace "nbbs_index_data"
    "ix_objects_type_lastmodified" btree ("type", lastmodified),
tablespace "nbbs_index_data"
    "ix_objects_user1" btree (upper(user1::text)), tablespace
"nbbs_index_data"
    "ix_objects_user2" btree (upper(user2::text)), tablespace
"nbbs_index_data"

Work_mem=64mb, r_p_c = 2 on the session gave similar execution plan
except the cost different due to change r_p_c.

                                                               QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
 Limit  (cost=0.00..5456.11 rows=501 width=912)
   ->  Index Scan Backward using ix_objects_type_lastmodified on objects
(cost=0.00..253083.03 rows=23239 width=912)
         Index Cond: (("type")::text = 'cpe'::text)
         Filter: ((domainid)::text = ANY
(('{HY3XGEzC0E9JxRwoXLOLbjNsghEA,3330000000000000000000000000}'::charact
er varying[])::text[]))
(4 rows)


Given the nature of the ix_objects_type_lastmodified index, wondering if
the index requires rebuilt. I tested rebuilding it in another db, and it
came to 2500 pages as opposed to 38640 pages.

The puzzle being why the same query with same filters, runs most of
times faster but at times runs 5+ mintues and it switches back to fast
mode. If it had used a different execution plan than the above, how do I
list all execution plans executed for a given SQL.

Thanks,
Stalin

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Monday, August 03, 2009 1:45 PM
To: Subbiah Stalin-XCGF84; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Query help

"Subbiah Stalin-XCGF84" <SSubbiah@Motorola.com> wrote:

> Shared buffer=8G, effective cache size=4G.

That is odd; if your shared buffers are at 8G, you must have more than
4G of cache.  How much RAM is used for cache at the OS level?
Normally you would add that to the shared buffers to get your effective
cache size, or at least take the larger of the two.

How much RAM is on this machine in total?  Do you have any other
processes which use a lot of RAM or might access a lot of disk from time
to time?

> Let me know if you need any other information.

The \d output for the object table, or the CREATE for it and its
indexes, would be good.  Since it's getting through the random reads by
the current plan at the rate of about one every 5ms, I'd say your drive
array is OK.  If you want to make this query faster you've either got to
have the data in cache or it has to have reason to believe that a
different plan is faster.

One thing which might help is to boost your work_mem setting to
somewhere in the 32MB to 64MB range, provided that won't drive you into
swapping.  You could also try dropping the random_page_cost to maybe 2
to see if that gets you a different plan.  You can do a quick check on
what plans these generate by changing them on a given connection and
then requesting just an EXPLAIN of the plan, to see if it's different.
(This doesn't actually run the query, so it's fast.)

-Kevin