Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Saturación PostgreSQL

SELECT S.phoneid, S.sequenceid
FROM d_campaigns C, d_phonestatus S
WHERE C.campaignid IN (17,16,15,14,13) AND C.campaignid=S.campaignid AND
S.sequenceid=(

SELECT MAX(S2.sequenceid) FROM d_phonestatus S2 WHERE
S2.phoneid=S.phoneid )

AND (S.assignedto IS NULL OR (S.assignedto<>'A436MA' AND
(S.status='rejected' OR
S.status='error')))
AND (S.callbackdate <= NOW() OR S.callbackdate IS
NULL)
ORDER BY S.callbackdate, C.priority DESC, S.phoneid LIMIT 1

El 14 de julio de 2010 23:05, Silvio Quadri <silvioq(at)gmail(dot)com> escribió:

> El 14 de julio de 2010 17:43, OgiSer Tamade <tamade(dot)ogiser(at)gmail(dot)com>
> escribió:
> >
> > Hola,
> >
> > adjunto el explain, se hace 2 vacuum al día. El servidor tiene 4 nucleos
> vendor_id, hoy ejemplo me ha pasado con 95 usuarios, si bajamos a 75
> funciona correctamente. Vuelvo a adjuntar el postgree.conf
> >
> > : GenuineIntel
> > cpu family : 6
> > model : 23
> > model name : Intel(R) Xeon(R) CPU X3330 @ 2.66GHz
> > stepping : 10
> > cpu MHz : 1998.000
> > cache size : 3072 KB
> >
> >
> > Limit (cost=702.42..702.43 rows=1 width=121)
> >
> > -> Sort (cost=702.42..702.43 rows=1 width=121)
> >
> > Sort Key: s.callbackdate, c.priority, p.phoneid
> >
> > -> Nested Loop (cost=321.26..702.41 rows=1 width=121)
> >
> > Join Filter: ("inner".campaignid = "outer".campaignid)
> >
> > -> Nested Loop (cost=321.26..701.23 rows=1 width=97)
> >
> > -> Bitmap Heap Scan on phonestatus s
> (cost=321.26..695.25 rows=1 width=16)
> >
> > Recheck Cond: ((assignedto = 'A527MA'::text)
> AND (status = 'assigned'::text))
> >
> > Filter: (((callbackdate <= now()) OR
> (callbackdate IS NULL)) AND (sequenceid = (subplan)))
> >
> > -> BitmapAnd (cost=321.26..321.26 rows=42
> width=0)
> >
> > -> Bitmap Index Scan on
> phonestatus_assignedto_index (cost=0.00..73.66 rows=2473 width=0)
> >
> > Index Cond: (assignedto =
> 'A527MA'::text)
> >
> > -> Bitmap Index Scan on
> phonestatus_status_index (cost=0.00..247.36 rows=7816 width=0)
> >
> > Index Cond: (status =
> 'assigned'::text)
> >
> > SubPlan
> >
> > -> Result (cost=5.01..5.02 rows=1 width=0)
> >
> > InitPlan
> >
> > -> Limit (cost=0.00..5.01 rows=1
> width=4)
> >
> > -> Index Scan Backward using
> phonestatus_pkey on phonestatus s2 (cost=0.00..10.03 rows=2 width=4)
> >
> > Index Cond: (phoneid =
> $0)
> >
> > Filter: (sequenceid IS
> NOT NULL)
> >
> > -> Index Scan using phonenumbers_pkey on
> phonenumbers p (cost=0.00..5.97 rows=1 width=85)
> >
> > Index Cond: (p.phoneid = "outer".phoneid)
> >
> > Filter: ((campaignid = 31) OR (campaignid = 30)
> OR (campaignid = 29) OR (campaignid = 28) OR (campaignid = 26))
> >
> > -> Seq Scan on campaigns c (cost=0.00..1.08 rows=8
> width=32)
> >
> > El 13 de julio de 2010 16:43, Silvio Quadri <silvioq(at)gmail(dot)com>
> escribió:
>
> Estaría bien ver la consulta.
>
> Algunos tips.
> 1. ¿Probaste con un índice assignedto / status?
> 2. El status convendría que lo pases a numérico. Si no lo usás para
> otra cosa, en esta consulta ese índice está de más y podrías
> eliminarlo.
> 3. ¿Callbackdate puede ser futuro?
>
> Silvio
>