From: | "Subbiah Stalin-XCGF84" <SSubbiah(at)Motorola(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Query help |
Date: | 2009-08-03 17:09:40 |
Message-ID: | BF8D37611DA14544B3A47B8FF0559446045FE878@ct11exm61.ds.mot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
All,
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.
Here is explain analyze out of the query.
SELECT
OBJECTS.ID,OBJECTS.NAME,OBJECTS.TYPE,OBJECTS.STATUS,OBJECTS.ALTNAME,OBJE
CTS.DOMAINID,OBJECTS.ASSIGNEDTO,OBJECTS.USER1,OBJECTS.USER2,
OBJECTS.KEY1,OBJECTS.KEY2,OBJECTS.KEY3,OBJECTS.OUI,OBJECTS.PRODCLASS,OBJ
ECTS.STATUS2,OBJECTS.LASTMODIFIED,OBJECTS.LONGDATA,OBJECTS.DATA0,
OBJECTS.DATA1
FROM OBJECTS
WHERE OBJECTS.DOMAINID IN
('HY3XGEzC0E9JxRwoXLOLbjNsghEA','3330000000000000000000000000')
AND OBJECTS.TYPE IN ('cpe')
ORDER BY OBJECTS.LASTMODIFIED DESC LIMIT 501
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------
Limit (cost=0.00..9235.11 rows=501 width=912) (actual
time=0.396..2741.803 rows=501 loops=1)
-> Index Scan Backward using ix_objects_type_lastmodified on objects
(cost=0.00..428372.71 rows=23239 width=912) (actual time=0.394..2741.608
rows=501 loops=1)
Index Cond: (("type")::text = 'cpe'::text)
Filter: ((domainid)::text = ANY
(('{HY3XGEzC0E9JxRwoXLOLbjNsghEA,3330000000000000000000000000}'::charact
er varying[])::text[]))
Total runtime: 2742.126 ms
The table is auto vaccumed regularly. I have enabled log_min_messages to
debug2 but nothing stands out during the times when the query took 5+
minutes. Is rebuild of the index necessary here.
Thanks in Advance,
Stalin
Pg 8.2.7, Sol10.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-03 18:56:37 | Re: PostgreSQL 8.4 performance tuning questions |
Previous Message | Scott Carey | 2009-08-03 17:00:26 | Re: PostgreSQL 8.4 performance tuning questions |