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.
