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,OBJECTS.DOMAINID,OBJECTS.ASSIGNEDTO,OBJECTS.USER1,OBJECTS.USER2,
OBJECTS.KEY1,OBJECTS.KEY2,OBJECTS.KEY3,OBJECTS.OUI,OBJECTS.PRODCLASS,OBJECTS.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}'::character 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.
